How To: Migrate a PostgreSQL database

How to migrate a PostgreSQL database from one server to another

Michael Tue 21 April 2026 Tue 21 April 2026 5 mins
technical how-to

This article will show you how to migrate a database from one PostgreSQL server to another.

Before starting the database migration, stop the service that is accessing the database to prevent a corrupt backup and/or missing data. This should also flush any cached data to the disk.

On the old server

Dump the database contents from the old database to a file.

pg_dump [-h <ip_address>] -U <user> -d <database> -f <backup_file>.sql
  • pg_dump – This is a PostgreSQL utility command that dumps all the data from a PostgreSQL database to a file. This includes not only the data but also indexes, schema, autoincrement states, and everything else that represents the state of the database at the time the backup was made.
  • -h <ip_address> – This specifies where to find the PostgreSQL database to dump from. If this command is run on the same machine as the database, it can be omitted and will default to the current machine.
  • -U <user> – This is the username of the SQL user to access the database. Make sure it has permissions to access the database.
  • -d <database> – The name of the database to backup.
  • -f <backup_file>.sql – This specifies the name of the file to dump the database to. Note that the output of this command is plaintext SQL statements, so the file extension should be .sql.

The contents of the backup file is plaintext SQL, so you can view and modify the backup if desired. This can be helpful to adjust usernames, database names, and permissions if you want them to be different on the new server than the old one. When the backup is restored, it will execute every line in the file one-by-one to recreated the database. Any changes to the backup will be executed as well.

On the new server

First, connect to the new database.

sudo -u postgres psql

Create the database user.

CREATE USER <db_user> WITH PASSWORD '<password>';

Create the database.

CREATE DATABASE <db_name> OWNER <db_user>;

Finally, grant the new user all permissions and privileges on the newly created database.

GRANT ALL ON SCHEMA public TO <db_user>;

Once the user and database are created in the new server, you can exit the PostgreSQL command line (\q). The next step is to configure PostgreSQL to allow access to the new database for this user. Open the access configuration file for PostgreSQL in any editor.

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

And add a line like the following:

# TYPE   DATABASE        USER            ADDRESS            METHOD
<type>   <database>      <db_user>       <ip_address>       <auth_type>
  • <type> – This specifies the type of connection. Valid options include local, host, hostssl, and hostnossl. local is for connections over the PostgreSQL Unix socket. host is for TCP/IP connections (either remote or via localhost). hostssl and hostnossl are for specifically TCP/IP conections that are required to have SSL encryption or not.
  • <database> – This is the name of the database this record allows access to.
  • <db_user> – This is the user that is being allowed access to the above database.
  • <ip_address> – This is the IP address from which the specified user is allowed to connect to the specified database. The format should be IP address and subnet. A subnet of /32 corresponds to a single IP address, so something like 127.0.0.1/32 would correspond to only allowing connections from localhost over IPv4. For access from Docker containers, which have dynamic IP addresses, you should whitelist the entire Docker subnet: 172.22.0.0/16.
  • <auth_type> – This is the type of authentication required for this method of access. Options include trust, reject, md5, password, scram-sha-256, and peer, among others.
  • trust will implicitly trust this connection and not require any authentication at all.
  • reject will reject this connection. This is useful in conjuction with other records, which allow you to fine tune access. Records are evaluated from top to bottom and terminate on the first match, so you can reject a specific IP address at the top of the file, and then accept a broader range of IP addresses lower in the file.
  • md5 will accept a password with SCRAM-SHA-256 or MD5 hashing algorithm. MD5 is easily cracked with modern machines and is not considered secure. This authentication method is deprecated and will be removed in future version of PostgreSQL.
  • password requires the client to send their password unencrypted to the server. The password is transmitted in cleartext, so this authentication method is not recommended on untrusted networks.
  • scram-sha-256 will required strong encryption for the password.
  • peer authentication will check if the user connecting has an OS account that matches the name of a database user. If so, it will allow the connection. This authentication method is only valid on local connection types.

If you intend to allow connections from other devices or networks, you must change PostgreSQL’s configuration to bind to all network interfaces. By default it is only bound to localhost, and connections from another network (e.g. another computer or even Docker running on the same machine) will fail.

To configure PostgreSQL to bind to all available interfaces, modify the config file at /etc/postgresql/<version>/main/postgresql.conf and change the following line:

listen_addresses = '*'

After saving the changes to the configuration, trigger a reload to force PostgreSQL to update its configuration. If you changed the listen_addresses config, you must restart the PostgreSQL service instead of just reloading it.

sudo systemctl reload postgresql

Finally, restore the database backup to the new database.

psql [-h <ip_address>] -U <user> -d <database> -f <backup_file>.sql
  • psql – This is the command that connects to a PostgreSQL server.
  • -h <ip_address> – Host of the new database server. If the backup file was copied to the new server and this command is being run on the same device as the database being restored to, this can be omitted.
  • -U <user> – The username to connect with. Should be the same as the old one.
  • -d <database> – The database name. Should be the same as the old one.
  • -f <backup_file>.sql – The file containing the backed up data created from pg_dump.

Now the data should be restored to the new database. Point the service to the new database and start it up, and it should connect and appear that nothing has changed.


Read more:
Related posts: