Data migrations, or creating a lower level test environment with same data has a requirement, exporting and importing a SQL dump into PostgreSQL database. Today we will cover importing SQL dump into PostgreSQL database.

TL;DR pg_restore –dbname=database_name /tmp/path/to/dump.sql

Importing SQL Dump

Either you need to restore data or you want to create a lower level environment with exported data, you need to import data to PostgreSQL database. Dump files also can be used for point in time recovery.

What is a SQL Dump?

Simply, it is a file written in SQL format (CREATE TABLE, CREATE INDEX, INSERT INTO etc…) that includes all object definitions and data. SQL dumps also is a backing up method for PostgreSQL databases. As dump files written in SQL format, that makes them portable. Even you can move them between different endian bits.

How to Dump a Database?

We are not supposed to cover dumping a database into SQL file topic here but to note, just wanted to share the method.

pg_dump -U postgres -W -F t postgres > /tmp/backup/backup."$(date +%Y-%m-%d_%H-%M-%S.txt)"
postgres@53a79e39431f:~$ ls -al /tmp/backup/
total 16
drwxr-xr-x 2 postgres postgres 4096 Jun 25 07:08 .
drwxrwxrwt 1 root     root     4096 Jun 25 07:08 ..
-rw-r--r-- 1 postgres postgres 5120 Jun 25 07:08 backup.2023-06-25_07-08-11.txt

You can read more about pg_dump command from this link on PostgreSQL documentation.

Import SQL Dump Into PostgreSQL Database

I don’t recall how many articles i wrote to this blog. Almost every time, i mention “you have several options to achieve this”. Yes, again. There are multiple ways to import a SQL Dump into PostgreSQL database.

pg_restore is the command to import dump files into PostgreSQL. To learn more about pg_restore command and read it’s syntax from PostgreSQL documentation.

pg_restore --dbname=database_name /tmp/path/to/dump.sql

Also you can use psql to import dump files into PostgreSQL database.

psql database_name < /tmp/path/to/dump.sql

The example above gives the SQL files to psql as an input. Also dash f parameter can be used to achieve the same.