How to load a sample database with schemas and data in PostgreSQL

 

In this article, we are going to cover the types of backups in PostgreSQL that we can use to load a sample database with schemas and data.

Database SQL Dump 

The pg_dump utility takes a SQL script backup of a single database from your database cluster. The pg_dump runs when the server is up and running without blocking any reading or writing activity on the servers. It does not require any special privilege; even a normal user can run it provided that user has permission on that particular database. The dumps taken by pg_dump are totally consistent, because the dump is a snapshot of your database and its data at the time when the pg_dump begins running. 

Syntax

pg_dump [options] [dbname]

 

For more options, you can use the following command:

<PG_BIN>/pg_dump --help



[postgres@localhost bin]$ pwd

/usr/pgsql-12/bin



[postgres@localhost bin]$ ./pg_dump --version

pg_dump (PostgreSQL) 12.2

 

Example 

Backup a single database with its schema and data. 

$ ./pg_dump -h localhost -p 5432 -U postgres db1 -f /tmp/plaintxtdump.sql

 

Since the above dump is a plain text dump, it can be restored using psql.

To restore a plain text dump the following command can be used:

$ ./psql -f /tmp/plaintxtdump.sql -d restoredb -p 5432 -U postgres 

SET

SET

SET

SET

SET

 set_config 

------------



(1 row)



SET

SET

SET

SET

SET

SET

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

COPY 100

COPY 200

 

Dump Formats 

In the examples below, we will see how to take dump in various formats like tar, custom, and directory format:

-F, --format=c|d|t         output file format (custom, directory, tar)

 

The default format for pg_dump is plain text. You can also use the option -Fp.

Tar format

To dump a database into tar format, use the following command:

$ ./pg_dump -h localhost -p 5432 -U postgres db1 -Ft -f /tmp/tarformatdump.tar

 

To restore the above tar format dump:

$ ./pg_restore -Ft -d restoredb1 -U postgres /tmp/tarformatdump.tar

 

After restoring, you can cross-validate the restored database objects.

postgres=# \c restoredb1

You are now connected to database "restoredb1" as user "postgres".

restoredb1=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

--------+------+-------+----------

 public | t1   | table | postgres

 public | t2   | table | postgres

(2 rows)

 

Compressed format

To dump a database into compressed format, use the following command:

$ ./pg_dump -Fc -h localhost -p 5432 -U postgres db1 -f /tmp/compressedformatdump.dump

 

NOTE: You can use the -Z option for compression level. The level options are 0 to 9.

To restore the compressed format dump:

$ ./pg_restore -Fc -d restoredb2 -U postgres /tmp/compressedformatdump.dump

 

Directory format

To dump a database into directory format, use the following command:

$ ./pg_dump -Fd -h localhost -p 5432 -U postgres db1 -f /tmp/backupdirformat

 

To restore the directory format, tell pg_restore to create the named database for restoring. To achieve this use option -C.

The -l command will list the table of contents, which you can use to find the db name.

[postgres@localhost bin]$ ./pg_restore -Fd -l /tmp/backupdirformat

;

; Archive created at 2020-03-03 12:43:45 IST

;     dbname: db1

;     TOC Entries: 8

;     Compression: -1

;     Dump Version: 1.14-0

;     Format: DIRECTORY

;     Integer: 4 bytes

;     Offset: 8 bytes

;     Dumped from database version: 12.2

;     Dumped by pg_dump version: 12.2

;

;

; Selected TOC Entries:

;

202; 1259 16385 TABLE public t1 postgres

203; 1259 16388 TABLE public t2 postgres

3682; 0 16385 TABLE DATA public t1 postgres

3683; 0 16388 TABLE DATA public t2 postgres

 

You can either drop the existing database or restore on another port.

$  ./pg_restore -Fd -C -d postgres -p 5432 -U postgres /tmp/backupdirformat

 

NOTE: In the above example, the database ‘db1’ will be created with the use of -C option, and -d is to just specify the existing database for connection.

$ ./psql -d postgres -p 5432

psql (12.2)

Type "help" for help.



postgres=# \c db1

You are now connected to database "db1" as user "postgres".





db1=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

--------+------+-------+----------

 public | t1   | table | postgres

 public | t2   | table | postgres

(2 rows)

 

​

Popular Links

• Connecting PostgreSQL using psql and pgAdmin
• How to use PostgreSQL with Django
• 10 Examples of PostgreSQL Stored Procedures
• How to use PostgreSQL with Laravel
• How to use tables and column aliases...

Featured Links

• PostgreSQL vs. SQL Server (MSSQL)...
• The Complete Oracle to PostgreSQL Migration...
• PostgreSQL vs. MySQL: A 360-degree Comparison...
• PostgreSQL Replication and Automatic Failover...
• Postgres on Kubernetes or VMs: A Guide...