PostgreSQL 14: Database migration with pg_dump
Tags: postgres, pgdump, database, migration
Published: 2023-01-04 15:04:09

## Summary

[PostgreSQL](https://www.postgresql.org/) 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)

```sh
pg_dump dbname > dumpfile
```

### Import (restoration)

```sh
psql dbname < dumpfile
```

You must create database where you migrate to beforehand.

### Environment

- OS: [OpenBSD](https://www.openbsd.org/) [7.1](https://www.openbsd.org/71.html)
- Database: PostgresSQL [14](https://www.postgresql.org/docs/14/index.html)

---

## 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.

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

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

```console
Enter password for new role: 
Enter it again: 
```

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

```console
Password:
```

#### Create database

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

```console
$ 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:

```console
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`:

```console
$ 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:

```console
$ 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.

```console
$ gzip {EXPORTED_FILE}.sql
```

Then deliver it to the new server with [`scp`](https://man.openbsd.org/scp.1) or another.

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

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

```console
$ gunzip {EXPORTED_FILE}.sql.gz
```

#### Import

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

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

The password will be required:

```console
Password for user {DB_OWNER}: 
```

The output was in my case:

```sql
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:

- [PostgreSQL Documentation - SQL Dump](https://www.postgresql.org/docs/current/backup-dump.html#BACKUP-DUMP-RESTORE)
- [PostgreSQLTutorial.com](https://www.postgresqltutorial.com/postgresql-administration/postgresql-copy-database/)

---

Happy storing 🕊