How to Create a PostgreSQL User
PostgreSQL
04.07.2024
Reading time: 4 min
Share
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.
psqlIn 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
pgAdminand 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 theCONNECTION LIMITto-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_monitorrole 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
04.07.2024
Reading time: 4 min
Share
Similar
PostgreSQL
Logical Replication in PostgreSQL
When deploying applications, having more than one copy of the database is always beneficial. After creating copies, it is essential to ensure they are all synchronized. The process of synchronizing database copies is called replication.
Logical replication in PostgreSQL refers to the synchronization of copies without being tied to a specific physical data representation on a disk. It is independent of processor architecture, platform, or database system version. Synchronization is performed based on a replication identifier, which is typically the primary key.
Logical replication uses a publish-and-subscribe model.
Replication Process
In general, the replication process consists of the following steps:
Creating one or more publications on the publisher node.
Subscribing one or more subscribers to one or more publications.
Copying a snapshot of the publisher's database to the subscriber. This step is also known as the table synchronization phase. It is possible to create multiple table synchronization workers to reduce the time required for this phase. However, there can only be one synchronization process for each table.
Sending the subsequent changes made on the publisher node to the subscriber node. These changes are applied in the commit order to ensure transactional consistency.
The subscriber node fetches changes as they occur in the publisher's database in real-time, ensuring that the subscriber and publisher databases remain synchronized.
This mechanism ensures up-to-date data consistency across the replicated databases.
Logical Replication in Practice
Suppose you want to set up logical replication on a single host. To achieve this, use different ports—for example, the publisher will operate on port 5432, and the subscriber on port 5431.
Edit the Configuration File
Start by editing the PostgreSQL configuration file:
sudo nano /etc/postgresql/10/main/postgresql.conf
Uncomment the wal_level parameter and set it to logical. It should look like this:
wal_level = logical
Save and close the configuration file, then restart PostgreSQL:
sudo systemctl restart postgresql
Export Global Objects
On the master, execute the following command for the main database:
pg_dumpall --database=postgres --host=192.168.1.2 --no-password --globals-only --no-privileges | psql
The pg_dumpall command exports databases in script format.
The --database parameter specifies the database used for connecting and exporting global objects and locating other databases. By default, it uses the postgres database.
The --globals-only parameter ensures only global objects are exported, excluding the database contents.
For detailed information, consult the PostgreSQL documentation.
Export Schema on the Replica
On the replica, run:
pg_dump --dbname=db_name --host=192.168.1.2 --no-password --create --schema-only | psql
Prepare Data for Testing
Create a test table with two columns:
CREATE TABLE table1(x int primary key, y int);
The x column will store the primary key.
The y column will store integer values.
Insert a sample row:
INSERT INTO table1 VALUES(10, 11);
At this point, the table contains a single row where the primary key is 10 and the value is 11. This minimal dataset is enough to verify synchronization.
Create a Publication on the Master
Create a publication that replicates the desired table:
CREATE PUBLICATION my_publication FOR TABLE table1;
The FOR TABLE parameter allows you to specify which tables to replicate.
You can limit the changes to be published or include additional tables later.
To create a publication for all existing and future tables, use the ALL TABLES parameter. For more details, refer to the PostgreSQL documentation.
The publication named my_publication is ready. Now it’s time to create a subscription on port 5431.
Recreate the Table on the Subscriber Node
On the subscriber, create the same table structure as on the publisher:
CREATE TABLE table1(x int primary key, y int);
Create a Subscription on the Replica
Create a subscription named my_subscription:
CREATE SUBSCRIPTION my_subscription CONNECTION 'host=localhost port=5432 dbname=postgres' PUBLICATION my_publication;
Verify Synchronization
Query the table on the subscriber:
SELECT * FROM table1;
This command will display the rows synchronized from the publisher. Initially, it should return the row added earlier (11 with the primary key 10).
How It Works
The CREATE SUBSCRIPTION command creates a subscription for the current database, which begins receiving logical changes from the publication my_publication.
Upon execution, a logical replication worker is created to fetch changes from the publisher.
On the publisher side, a walsender process starts to read the WAL (Write-Ahead Log), decode changes, and send them to the subscriber.
To test the synchronization, add additional rows on the publisher:
INSERT INTO table1 VALUES(20, 21), (30, 31);
Verify that the subscriber displays these rows:
SELECT * FROM table1;
If you have multiple servers, additional configuration is required.
Allow Connections on the Publisher
On the main server, edit the configuration file to listen on the private IP address:
sudo nano /etc/postgresql/10/main/postgresql.conf
Locate the listen_addresses parameter and modify it to include the private IP address of the master:
listen_addresses = 'localhost, MASTER_PRIVATE_IP'
Configure Access Control
Edit the pg_hba.conf file on the publisher to allow incoming connections from the replica:
sudo nano /etc/postgresql/10/main/pg_hba.conf
Add the following line, replacing REPLICA_PRIVATE_IP with the actual private IP address of the replica:
host replication postgres REPLICA_PRIVATE_IP/32 md5
Look for the comment:
# If you want to allow non-local connections, you need to add more.
Add your new rule below this line.
Firewall Configuration
On the publisher, allow traffic from the replica to port 5432:
sudo ufw allow from REPLICA_PRIVATE_IP to any port 5432
Apply Changes
Restart PostgreSQL to apply all changes:
sudo systemctl restart postgresql
Troubleshooting Issues
If replication doesn’t seem to work, check the PostgreSQL logs on the replica for possible errors. The log file is typically located at: /var/log/postgresql/postgresql-10-main.log.
Common Issues and Solutions:
Private Network Not Enabled. Ensure both servers are in the same private network or correctly configured for cross-network access.
Incorrect IP Address Configuration. Verify that the server is listening on the correct private network IP address.
wal_level Not Set to logical. Double-check the wal_level parameter in the PostgreSQL configuration.
Firewall Blocking Connections. Confirm that the firewall is not blocking incoming connections on the required port (e.g., 5432).
Mismatch in Table or Field Names. Ensure that table and column names match the publisher and subscriber exactly.
Table Not Included in the Publication. Verify that the table is added to the publication on the publisher.
After addressing these issues, replication should resume automatically. If not, drop the existing subscription and recreate it:
DROP SUBSCRIPTION my_subscription;
Physical Replication Overview
PostgreSQL supports two types of replication: logical (discussed above) and physical replication. Here's a brief overview of physical replication.
Key Features of Physical Replication:
Introduced in PostgreSQL 9.0. Physical replication synchronizes databases at the file level.
Block-Level Synchronization. Changes are tracked using precise block addresses and replicated byte-by-byte.
Write-Ahead Log (WAL). Changes from the master are transmitted via WAL and applied on the standby server.
Limitations of Physical Replication:
No Partial Database Replication: You cannot replicate only a portion of the database.
High Overhead: All changes are transmitted, potentially increasing network load.
Platform Restrictions: Physical replication requires identical server platforms, including CPU architecture (e.g., Windows to Windows or Linux to Linux).
Version Compatibility: Databases on different PostgreSQL versions cannot synchronize.
Conclusion
This guide covered setting up and managing logical replication in PostgreSQL, including troubleshooting common issues. We also briefly touched on physical replication, highlighting its characteristics and limitations.
For simplified database management, consider cloud database services like Hostman, which offers managed PostgreSQL and other database solutions to streamline deployment and scaling.
11 December 2024 · 7 min to read
PostgreSQL
How to Backup and Restore PostgreSQL Databases with pg_dump
PostgreSQL is a robust, open-source relational database mostly used for web applications and data storage solutions. Ensuring data recovering from unexpected losses is essential, and PostgreSQL offers powerful tools for backup and restoration. In this guide, we’ll walk through the process of backing up and restoring PostgreSQL databases using the pg_dump and pg_restore commands.
Creating a Sample Database
To walk through this backup and restore process with PostgreSQL, let’s first create a sample database called shop_inventory, populate it with some tables and data, and then demonstrate the pg_dump and pg_restore commands in real-world scenarios.
If PostgreSQL is not installed, you can do it with:
sudo apt install postgresql -y
After the installation is complete, switch to the postgres user:
sudo -i -u postgres
Then, start by connecting to PostgreSQL:
psql -U postgres
Inside the PostgreSQL prompt, create the shop_inventory database:
CREATE DATABASE shop_inventory;
Generate Tables and Insert Sample Records
Establish a connection to the shop_inventory database:
\c shop_inventory;
Then create the tables: customers, products, and orders.
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(100),
price NUMERIC(10, 2)
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
product_id INT REFERENCES products(product_id),
quantity INT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Populate these tables with sample data:
INSERT INTO customers (name, email) VALUES
('Alice Johnson', '[email protected]'),
('Bob Smith', '[email protected]');
INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Smartphone', 800.00);
INSERT INTO orders (customer_id, product_id, quantity) VALUES
(1, 1, 2),
(2, 2, 1);
Now that we have our shop_inventory database set up, we’re ready to back up and restore it.
Backup a Single PostgreSQL Database
The pg_dump command enables you to create backups in various formats, which can be restored using pg_restore or psql. The following examples demonstrate backing up our shop_inventory database using different options.
pg_dump -U [username] -d [database_name] -f /path/to/backup_file.sql
For shop_inventory database:
mkdir backups &&pg_dump -U postgres -d shop_inventory -F c -f backups/shop_inventory_backup.custom
-F c specifies the format of the backup. -F stands for "format," and c denotes "custom format." The custom format is specific to PostgreSQL and creates a compressed, non-textual backup file. This format is useful for selective restoration because it allows pg_restore to restore individual tables or objects from the backup.
This command creates a PostgreSQL file containing the structure and data of the shop_inventory database.
Full Databases Backup (Full Instance Backup)
The pg_dumpall command can back up all databases, roles, and configurations in a single file.
pg_dumpall -U [username] -f /path/to/all_databases_backup.sql
Example:
pg_dumpall -U postgres -f backups/full_postgres_backup.sql
This creates a file in SQL format that includes all databases, allowing you to restore the entire PostgreSQL setup.
Backup a Remote PostgreSQL Database
To back up a database hosted on a remote server, use the -h option with pg_dump to specify the host.
pg_dump -U [username] -h [host] -d [database_name] -f /path/to/backup_file.sql
Example for shop_inventory on a remote server:
pg_dump -U postgres -h remote_host -d shop_inventory -f backups/remote_shop_inventory_backup.sql
Make sure the remote server allows connections and that the user has sufficient privileges.
Restore an Individual PostgreSQL Database
To restore a backup, it’s often necessary to drop the existing tables to avoid conflicts, especially if the table structures or data have changed. Here’s a guide on how to drop tables in shop_inventory and then restore them from a backup.
You can drop all tables in shop_inventory either manually or with a command that removes all tables at once. This example shows how to drop all tables using a single psql command.
psql -U postgres -d shop_inventory
In the psql prompt, run the following command to generate a list of DROP TABLE statements and execute them:
DO $$ DECLARE
r RECORD;
BEGIN
FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END $$;
This block drops each table in the public schema, along with any dependencies. Exit the psql prompt by typing:
\q
Now that the tables have been dropped, you can restore the shop_inventory database from your backup file.
Execute pg_restore to restore the database. Ensure that your backup was created with pg_dump using the -F c (custom) or -F t (tar) option.
pg_restore -U [username] -d [database_name] -1 /path/to/backup_file
Example:
pg_restore -U postgres -d shop_inventory -1 backups/shop_inventory_backup.custom
-1 executes the restore in a single transaction, which is helpful for rollback in case of an error.
The pg_restore command is employed to restore non-SQL format backups (e.g., custom or .tar format).
pg_restore -U [username] -d [database_name] -1 /path/to/backup_file
Restore All PostgreSQL Databases
For a full restore, you usually work with a backup created by pg_dumpall, which includes all databases, roles, and configurations. Before performing a full restore, you might want to drop and recreate all existing databases to avoid conflicts.
For our example, let’s drop the database shop_inventory then restore it:
psql -U postgresDROP DATABASE shop_inventory
If you backed up all databases with pg_dumpall, use psql to restore:
psql -U postgres -f backups/full_postgres_backup.sql
This command restores every database, role, and configuration as they were at the time of the backup.
Restore a PostgreSQL Database from a Remote Server
To restore a backup to a remote server, use the -h option with pg_restore or psql.
pg_restore -U [username] -h [host] -d [database_name] -1 /path/to/backup_file
For SQL files, use psql:
psql -U [username] -h [host] -d [database_name] -f /path/to/backup_file.sql
Make sure that network permissions and user access are configured correctly for the remote server.
Conclusion
By following these commands, you can back up and restore a PostgreSQL database like shop_inventory, ensuring data safety for your applications. Regular backups are vital, and testing your restore process is equally important to minimize downtime and data loss. With these tools, you can confidently manage PostgreSQL data in any scenario.
Hostman provides pre-configured and ready-to-use cloud databases, including cloud PostgreSQL.
12 November 2024 · 6 min to read
PostgreSQL
How to Migrate a PostgreSQL Database to Another Server
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.
04 July 2024 · 6 min to read
Do you have questions,
comments, or concerns?
Our professionals are available to assist you at any moment,
whether you need help or are just unsure of where to start.
whether you need help or are just unsure of where to start.
