How to use the command 'pg_dump' (with examples)

The ‘pg_dump’ command is used to extract a PostgreSQL database into a script file or other archive file. It provides a convenient way to backup or migrate a PostgreSQL database.

Use case 1: Dump database into an SQL-script file

Code:

pg_dump db_name > output_file.sql

Motivation: When you want to create a backup of your PostgreSQL database or migrate it to another server, you can use this command to dump the entire database into an SQL-script file. This file can then be used to recreate the database on another server.

Explanation:

• ‘pg_dump’ is the command for dumping a PostgreSQL database.
• ‘db_name’ is the name of the database you want to dump.
• ‘>’ is the shell redirection operator to redirect the output of the ‘pg_dump’ command to a file.
• ‘output_file.sql’ is the name of the file where the SQL-script will be stored.

Example output: The entire database ‘db_name’ will be dumped into the file ‘output_file.sql’ as an SQL-script.

Use case 2: Same as above, customize username

Code:

pg_dump -U username db_name > output_file.sql

Motivation: If you want to dump the database using a specific username, you can customize it using the ‘-U’ option. This is useful when you have different users with different privileges accessing the database.

Explanation:

• ‘-U username’ is the ‘-U’ option followed by the username you want to use for dumping the database.

Example output: The database ‘db_name’ will be dumped using the given ‘username’ into the file ‘output_file.sql’ as an SQL-script.

Use case 3: Same as above, customize host and port

Code:

pg_dump -h host -p port db_name > output_file.sql

Motivation: When you want to dump the database from a different host or a specific port, you can customize the host and port using the ‘-h’ and ‘-p’ options respectively. This is useful when you have a separate database server or need to connect to a different port.

Explanation:

• ‘-h host’ is the ‘-h’ option followed by the hostname or IP address of the database server.
• ‘-p port’ is the ‘-p’ option followed by the port number to connect to.

Example output: The database ‘db_name’ will be dumped from the specified ‘host’ and ‘port’ into the file ‘output_file.sql’ as an SQL-script.

Use case 4: Dump a database into a custom-format archive file

Code:

pg_dump -Fc db_name > output_file.dump

Motivation: By default, the ‘pg_dump’ command dumps the database into an SQL-script file. However, if you want to dump the database into a custom-format archive file, you can use the ‘-Fc’ option. This format is more flexible and allows for selective data restores.

Explanation:

• ‘-Fc’ is the ‘-Fc’ option to specify the custom-format archive file.

Example output: The database ‘db_name’ will be dumped into the file ‘output_file.dump’ in a custom-format archive.

Use case 5: Dump only database data into an SQL-script file

Code:

pg_dump -a db_name > path/to/output_file.sql

Motivation: Sometimes, you only need to dump the data from the database without the schema. In such cases, you can use the ‘-a’ option to dump only the database data into an SQL-script file.

Explanation:

• ‘-a’ is the ‘-a’ option to dump only the data from the database.

Example output: Only the data from the database ‘db_name’ will be dumped into the file ‘output_file.sql’ as an SQL-script.

Use case 6: Dump only schema (data definitions) into an SQL-script file

Code:

pg_dump -s db_name > path/to/output_file.sql

Motivation: If you want to dump only the schema or the data definitions of the database, excluding the data, you can use the ‘-s’ option. This is useful when you want to create a new database with the same structure as the original one.

Explanation:

• ‘-s’ is the ‘-s’ option to dump only the schema or data definitions.

Example output: Only the schema or data definitions from the database ‘db_name’ will be dumped into the file ‘output_file.sql’ as an SQL-script.

Conclusion:

The ‘pg_dump’ command is a versatile tool for backing up and migrating PostgreSQL databases. It provides several options to customize the dumping process to suit different requirements. By using these examples, you can effectively backup your data, migrate your database, or selectively restore data as needed.