Summary

PostgreSQL offers native ways to export database as backup from existing server and import to restore it to another one.

This post shows how to do it. It is really simple thanks to them.

It consists of a pair of key commands.

Export (backup)

pg_dump dbname > dumpfile

Import (restoration)

psql dbname < dumpfile

You must create database where you migrate to beforehand.

Environment

OS: OpenBSD 7.1
Database: PostgresSQL 14

Tutorial

Create database on new server

Create user

The user name must be the same to the owner of existing database. It is shown as {DB_OWNER} below.

$ createuser -U {DB_ADMIN} --port {DB_PORT} --pwprompt {DB_OWNER}

You will be asked of the password of the new user (role):

Enter password for new role: 
Enter it again:

Then the password of {DB_ADMIN} (the database administrator such as postgres) will be required:

Password:

Create database

As to below, {DB_OWNER} is that you created above. {DB_NAME} is the very database you are migrating.
Run:

$ createdb -U {DB_ADMIN} --port {DB_PORT} --encoding=UTF8 --locale=C --template=template0 --owner={DB_OWNER} {DB_NAME}

Then the password of {DB_ADMIN} will be required again:

Password:

Now your new database is ready.

Migrate

Export

Get the data from {DB_NAME} on {OLD_SERVER} and write it in {EXPORT_FILE}.sql:

$ pg_dump -U {DB_OWNER} -h {OLD_SERVER} {DB_NAME} > {EXPORTED_FILE}.sql

Next, I compressed the exported file and move it to the new server instead.

(Alternative) Direct import from the old server

Alternatively, you might import it to the new server directly with -h option:

$ psql -U {DB_OWNER} -h {NEW_SERVER} {DB_NAME} < {EXPORTED_FILE}.sql

Transfer

When it is big, you had better compress it before moving it.

$ gzip {EXPORTED_FILE}.sql

Then deliver it to the new server with scp or another.

$ scp {EXPORTED_FILE}.sql.gz new-server

Transfer is successful ?
Enter the new server, and decompress it.

$ gunzip {EXPORTED_FILE}.sql.gz

Import

It's the final step !! Supposed you are in the new server, run:

$ psql -U {DB_OWNER} -d {DB_NAME} -f {EXPORTED_FILE}.sql

The password will be required:

Password for user {DB_OWNER}:

The output was in my case:

SET
(...)
 set_config 
------------

(1 row)

SET
CREATE FUNCTION
(...)
CREATE TABLE
CREATE SEQUENCE
ALTER TABLE
(...)
COPY 20
(...)
 setval 
--------
   6820
(1 row)
(...)

ALTER TABLE
(...)

Reference

With many thanks to:

Happy storing 🕊

PostgreSQL 14: Database migration with pg_dump