How to Backup and Restore PostgreSQL Databases with pg_dump

When working with PostgreSQL, you might eventually need to transfer a database from one server to another. Despite seeming complex, PostgreSQL migration can be accomplished using PostgreSQL's built-in tools. This article outlines various methods to transfer a PostgreSQL database from one server to another on Ubuntu 22.04. Prerequisites To migrate a Postgres database, you will need: A current server running Ubuntu 22.04 with PostgreSQL installed. The database to be transferred should already exist in PostgreSQL. A new cloud server or virtual machine with Ubuntu 22.04 and cloud PostgreSQL pre-installed. This article uses PostgreSQL version 15. On Hostman, you can easily deploy such a server by choosing the PostgreSQL image when creating a server. Optionally, the pgAdmin client application for connecting and managing PostgreSQL databases. PgAdmin can be installed on any OS, including Windows, Linux, and macOS. Preparation Before Database Transfer Before starting the Postgres migration, ensure that both servers have: A network connection between them. Use tools like ping and telnet to verify this (with telnet, check ports 22 and 5432). Ports 22 (SSH) and 5432 (PostgreSQL) open. Enough free disk space. Configuring PostgreSQL for Remote Connections Ensure PostgreSQL can accept remote connections on both servers: Edit the postgresql.conf file. If using a different version, replace 15 with your version number: nano /etc/postgresql/15/main/postgresql.conf Find the listen_addresses parameter. By default, it is commented out and accepts only local connections (localhost). Allow remote connections from specific addresses or all addresses (for testing purposes, use *): listen_addresses = '*' In production environments, specify only the required addresses.  Save and exit the file. Edit the pg_hba.conf file: nano /etc/postgresql/15/main/pg_hba.conf Find the line for IPv4 local connections (# IPv4 local connections) and update it to allow connections from specific addresses or all addresses for testing: host all all 0.0.0.0/0 md5 Save and exit the file. Restart the PostgreSQL server: systemctl restart postgresql Set a strong password for the PostgreSQL user on both servers: sudo -i -u postgres psql In the psql console run: ALTER USER postgres WITH PASSWORD 'nsH7z*0kl>&7?7'; Where postgres is the username and nsH7z*0kl>&7?7 is the password. Setup and manage your cloud database with ease Transferring the Database Using pg_dump and psql Typically, transferring a database involves three steps: Creating a backup of the database. Transferring the backup to the target server. Restoring the database on the new server. These steps can be performed using PostgreSQL's pg_dump and psql tools. Transferring a Database For instance, let's look at transferring a database named e_commerce from a server with IP 166.1.227.252 to a server with IP 91.206.179.207. Create the target database beforehand: CREATE DATABASE e_commerce; Then run: pg_dump -h localhost -U postgres e_commerce | psql -h 91.206.179.207 -U postgres e_commerce Explanation: pg_dump creates a backup of the database. -h localhost specifies the database server address. -U postgres specifies the username. e_commerce is the database name on the current and new server.  psql connects to the remote PostgreSQL server and loads the database. -h 91.206.179.207 specifies the target server address. Transferring Without Remote Access If remote access is unavailable, save the database to a file, transfer it using scp, and restore it on the target server: pg_dump -h localhost -U postgres e_commerce > e_commerce.sql && scp e_commerce.sql [email protected]:/var/lib/postgresql When executing the command, the password for the postgres system user will be requested, not the password for the postgres user defined within the database. Where: pg_dump creates a database backup; -h localhost is the address of the server (IP address or domain name) where the database is located. In this example, the database server address matches the server address itself. If the database is on another server and network access is available, you can specify the address of the remote database; -U postgres is the username used to connect to the database; e_commerce is the name of the database to be transferred; e_commerce.sql is the name of the file in .sql format where the database will be saved; scp is a utility for secure file copying between hosts. It uses the SSH protocol for data transfer and protection; [email protected]:/var/lib/postgresql means username_on_remote_server@address_of_remote_server:full_path where the backup file will be saved. After entering the command, you first need to enter the password for the database user account (in this example, it is the postgres user), and then enter the password for the remote server user (in this example, it is the db-admin user). Now you need to upload the file to the database. Run these commands on the target server. Create a database in psql: CREATE DATABASE e_commerce; Then, exit psql and run in the terminal: psql e_commerce < e_commerce.sql Creating a Compressed Archive For larger databases, create a compressed archive: pg_dump -h localhost -U postgres e_commerce > e_commerce.tar.gzip && scp e_commerce.tar.gzip [email protected]:/var/lib/postgresql Restore from the archive: psql e_commerce < e_commerce.tar.gzip Adding a Timestamp to the Archive Name If you need to know the exact date and time that the database was backed up, you can include it in the name of the file being created.  To do this, use the date command and the date format. The example below will use the day-month-year date format: pg_dump -h localhost -U postgres e_commerce > e_commerce_$(date +%d-%m-%y).sql Transferring the Database Using pgAdmin Alternatively, you can use pgAdmin's graphical interface for the Postgres database migration. Backup Database Launch pgAdmin: Open pgAdmin and connect to your PostgreSQL server. Register Server: Right-click on Object Explorer, select Register, then Server. Configure Connection: Name: In the General tab, enter a name for the connection (e.g., my_db). Next, go to the Connection tab and specify: Host name/address: Specify the IP address or domain name of the PostgreSQL server. Port: Default is 5432; change if needed. Maintenance database: Name of the database for backup. Username and Password: Enter credentials for database access. Connect: Click Save to connect. If successful, the database appears on the left sidebar. Backup Database: Right-click on the database name and select Backup.   Set a Filename for the backup file. Choose a Format and Encoding (UTF8 recommended). Select specific Objects to include. Click Backup to start. Restore Database Prepare New Database: Open psql shell. Execute: CREATE DATABASE e_commerce; Connect to PostgreSQL Server: In pgAdmin, connect to the new PostgreSQL server, selecting e_commerce as the database. Restore Database: Right-click on the database name and choose Restore. Set the Format (ensure it matches the backup file). Specify the Filename of the backup file. Click Restore to begin. Wait for the Process completed confirmation. Virtual Servers and VPC with free 24/7 support Conclusion PostgreSQL offers several methods to migrate databases between servers, including using built-in tools for flexible and robust database backups and transfers.

To perform operations with databases in managed PostgreSQL, users with different access rights are created. This PostgreSQL tutorial will explain how to create users in PostgreSQL using different methods. You can create a PostgreSQL user in various ways: for example, by entering the CREATE USER (or CREATE ROLE) command in the command line or by executing an SQL query through an administrative interface such as pgAdmin. Let's take a closer look at these methods. Prerequisites To follow this guide, you will need PostgreSQL installed on your local machine or a cloud server.  If you are using Debian or Ubuntu, this tutorial will guide you through the installation. Alternatively, you can run PostgreSQL in a Docker container.  Creating Users in PostgreSQL with CREATE USER Let’s see how to create a superuser in Postgres with a password and administrator rights.,  Open the command line using the psql utility. psql In psql, enter the CREATE USER statement, replacing name and pass with the actual username and password you wish to use: CREATE USER name WITH PASSWORD 'pass' SUPERUSER; Note that you will need an administrator role with privileges to create users to execute this command. Executing this command will create a user with administrator rights, granting them full control over the database. To create a regular user, enter the following command, also replacing name and pass with your desired values: CREATE USER name WITH PASSWORD 'pass'; You can also specify additional parameters can when creating a user, such as connection restrictions or database access rights. This is done as follows: CREATE USER name WITH PASSWORD 'pass' CONNECTION LIMIT 5; The CONNECTION LIMIT option specifies the maximum number of simultaneous connections this user can establish. In this case, the user will be limited to 5 simultaneous connections to the database. Creating a PostgreSQL User through pgAdmin If you prefer to use an administrative interface such as pgAdmin, the creation of new users is done as follows: Open pgAdmin and connect to the PostgreSQL server. In the left navigation panel, select the database where you want to create a user. Right-click on Login/Group Roles and select Create — Login/Group Role. In the window that appears, fill in the following information: In the Name field on the General tab, enter the username. In the Password field on the Definition tab, enter the password for the new account. Additional settings such as the limit on the number of simultaneous connections (CONNECTION LIMIT—see the example above) and the expiration date and time of the account can also be configured on this tab. However, if the user is set to login without a password, the value specified in Account expires will not be applied. To set an unlimited number of simultaneous connections, set the CONNECTION LIMIT to -1. In the Privileges section, select the necessary privileges. For example, you can activate the Can login? option (by toggling the switch on the right) to allow the user to log in. You can also grant the account superuser rights (Superuser? option). On the Membership tab, you can assign users specific roles offered by the system. For example, the pg_monitor role is provided for tracking and diagnosing the database. Review the generated SQL code on the far right tab, then click Save to create the user with the selected parameters. The user will now be created and displayed in the Login/Group Roles list in pgAdmin. CREATE USER vs CREATE ROLE Roles in Postgres represent both individual users (with or without login rights) and entire groups. The commands CREATE ROLE and CREATE USER are used to create roles in the database. They perform the same function, but there is a slight difference in syntax. The CREATE ROLE command in Postgres is often (but not exclusively) used to create a role without login capability. This means the created role will not be able to connect to the PostgreSQL server and perform operations in the database (adding, modifying, and deleting data). This can be useful if you want to create a PostgreSQL Read Only user, for example: CREATE ROLE readonly_user; The CREATE USER command we showed above is often used to create a role with login capability. This means the created user will be able to connect to the PostgreSQL server and perform various operations in the database. Thus, the CREATE USER command is essentially synonymous with the CREATE ROLE command with the LOGIN option. That is, the commands CREATE USER readonly_user and CREATE ROLE readonly_user WITH LOGIN are equivalent.   That's all: now you know how to create users in various ways through the console and graphical interface and assign them different rights. Happy working!

PostgreSQL and Node.js are two powerful technologies that, when integrated, can form the backbone of highly efficient, scalable, and robust web applications.  One of the most popular open-source relational database systems, PostgreSQL is well known for its sophisticated features, dependability, and adherence to SQL standards. It provides sophisticated querying capabilities, broad data integrity, and a wide range of functional extensions. Because of its robust indexing, full-text search capabilities, support for ACID transactions, and capacity to manage massive data volumes, PostgreSQL is a popular database management system. Hostman provides pre-configured and ready-to-use cloud databases, including cloud PostgreSQL. Designed to create scalable network applications, Node.js is a JavaScript runtime based on the V8 JavaScript engine found in Chrome. Its lightweight and efficient I/O model is event-driven and non-blocking. Applications requiring real-time functionality and the ability to manage numerous concurrent connections with little overhead are especially well suited for Node.js. The JSON-centric approach of Node.js is enhanced by PostgreSQL's support for JSON and JSONB data types, which facilitates seamless data interchange and manipulation. PostgreSQL's advanced features, like full-text search, spatial data support with PostGIS, and custom data types, can greatly improve Node.js applications. Comprehensive security features are offered by both technologies. Strong data integrity, access control, and encryption are provided by PostgreSQL, and a range of security techniques and plugins are available in Node.js to safeguard application data. Importance of database access in applications Accessing a PostgreSQL database from a Node.js application is a key aspect of modern web and mobile application development. Through this integration, developers may take advantage of the strengths of both platforms to create scalable, secure, and effective applications. Here’s how to access PostgreSQL database from Node.js application:  Requirements A cloud server or a local machine running Ubuntu Root access or user with sudo privilege Node.js and npm installation After all requirements are met, proceed with the Node.js and npm installation. There are several ways to install Node.js and npm on Ubuntu. In this tutorial, the Node.js and npm will be installed on Ubuntu using the Ubuntu apt repository. Login to the terminal and run the command below. sudo apt update && apt upgrade  Run the command below to install Node.js and npm. sudo apt install nodejs npm After successful installation, verify the version by running the commands below respectively: nodejs --version npm --version PostgreSQL setup Install PostgreSQL and configure the database using the instructions below. Install the postgresql package along with its additional utilities. sudo apt install postgresql postgresql-contrib Use the systemctl start command to make sure the service is up and running. sudo systemctl start postgresql.service Verify the status by running the command below. sudo systemctl status postgresql.service Once the package is installed and up, login to PostgreSQL database using the default postgres user.  sudo -u postgres psql Create a new database.   CREATE DATABASE mydatabase; Enter your database name instead of mydatabase. Switch to the new database that has been created: \c mydatabase Create additional user to be used in the new database.  CREATE USER myuser WITH PASSWORD 'mypassword'; To enable the newly created user 'myuser' to be utilized, create an equivalent OS user. Execute the following command and respond to the prompts accordingly. adduser myuser Lastly, test the connection of the database created using the new user. Run the commands below respectively: sudo su - myuser psql -d mydatabase Creating a Node.js Project After successfully installing and configuring the PostgreSQL database, proceed to create the Node.js project. This project will facilitate the creation of a PostgreSQL database and interaction with npm configuration files. Create directory by running the command below: mkdir nodejs-pg-mydir Go to the newly created directory named nodejs-pg-mydir. Run the command below: cd nodejs-pg-mydir Initialize the directory by running the command below: npm init -y Installing Necessary Packages (pg) After creating the Node.js project, it's time to install the node-postgres module using the following command. npm install pg   Configuration Details To access the database using the application, follow these steps: Before verifying the connection, create a table on the database by running the command below. CREATE TABLE mytable( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, color VARCHAR(50) NOT NULL);   Testing the Example Connection Exit the database. The command prompt will go back to the project directory. Create a file called database.js in the nodejs-pg-mydir folder using nano virtual editor. Run the command nano database.js and paste the following code into it then save and exit. const { Pool } = require("pg"); const pool = new Pool({ user: "myuser", database: "mydatabase", password: "mypassword", port: 5432, host: "localhost", }); module.exports = { pool }; Note: Make sure to change 'user', 'database', and 'password' with the real database credentials. The module.exports = { pool }; line at the end of the file means that the pool variable can be used anywhere in the app. Test the connection by inserting data into the PostgreSQL database using the connection pool database.js, that was created on the previous step. Create a file datainsert.js and copy the below code. Save the file and exit. nano datainsert.js const { pool } = require("./database"); async function datainsert() { const [name, color] = process.argv.slice(2); const res = await pool.query( "INSERT INTO mytable (name, color) VALUES ($1, $2)", [name, color] ); console.log(`Added a mytable with the name ${name}`); } datainsert() Use the node command below to execute the file datainsert.js with argument name and color. node datainsert.js jcbrian brown The connection was successful if the output is similar to above. This means that the application can access the database with the table name mytable. To perform another test, try to retrieve data from the Postgres database and log them into the console. Create a file name dataretrieve.js and put the following code. Save and exit. nano dataretrieve.js const { pool } = require("./database"); async function dataretrieve() { try { const res = await pool.query("SELECT * FROM mytable"); console.log(res.rows); } catch (error) { console.error(error); } } dataretrieve() Run the command below to query on the database using Node.js. node dataretrieve.js  An output similar to above indicates that the query is completed without any error. Conclusion In conclusion, developers have an extensive number of options when integrating a PostgreSQL database with a Node.js application. Robust and scalable web applications can be developed because of the efficient data storage, retrieval, and manipulation made possible by the smooth connection between these technologies. Developers can leverage the flexibility of Node.js and the powerful features of PostgreSQL to create dynamic, data-driven apps that fit the needs of modern software development by following the methods suggested in this article. Accessing PostgreSQL via Node.js offers a significant benefit in developing high-performance applications when the appropriate tools and skills are applied.