Forbes cites leveraging data as the key to smarter business decisions. That said, more companies are relying on data to drive innovation and competitiveness.
Protecting that information is critical to ensuring leaders have reliable and updated data to inform their decision-making. The pg_dump utility is a PostgreSQL tool for backing up PostgreSQL databases.
In this tutorial, we’ll discuss the basics of the tool and how to use it to start backing up your databases.
The pg_dump utility is a command-line tool to export the data from a PostgreSQL database into a file. Extracting database objects such as tablespaces, schemas, individual tables, and views becomes efficient and effortless using pg_dump.
The tool requires elevated permissions. As such, the PostgreSQL superuser account invokes the process. The utility does not block other processes during the backup to avoid performance degradation. It continues to process new data in parallel.
The resulting file is useful in several ways:
Psql is a command-line interface to PostgreSQL. It allows you to run SQL commands to query the database, change data, explore schema, modify database objects, bulk load data, and more.
Furthermore, the pg_dump utility can now export data to a specified directory, load data from a specified directory, and load data from a specified file.
Administrators use psql to create pg_dump files. The syntax for the psql command is as follows:
psql [options] [database | dbname] [command | query]... [options]
The tool performs either a Hot or Cold snapshot before creating the backup.
When performing an export, pg_dump supports the following backup types:
The output of the utility is a file that contains either a standard export of data from the database or an SQL script file used to restore the data at a later time.
The utility supports saving dumps in various formats, including text format files with an ASCII format; compressed files using gzip (the default); and tar format.
The tool can also write dump files in raw binary format if you have special requirements.
A standard command will follow this format:
pg_dump [connection-option…] [option…] [dbname]
The following command shows how to perform a database backup on localhost.
pg_dump -h localhost -p 5432 -U postgres postgres > backup.sql
The connection-option will specify the hostname, port, user, and password to connect to the database.
The dbname will be the name of the database you want to dump. If omitted, the command dumps all databases on the server.
The -U switch tells pg_dump to use the database schema, not the table data.
The -h switch tells pg_dump to dump the entire database, even if it does not contain any data. The -p switch specifies the PostgreSQL port number.
The > specifies where to write the backup file. You can specify an absolute path or a relative path
The tool has several additional parameters useful when creating a database backup.
The command for a remote server is as follows:
pg_dump -h host_address> -p port> -U username> -d database>
host_address> is the IP address or hostname of the server
port> is the port number
username> is the database user’s name
database> is the database name
pg_dump -h 192.168.1.10 -p 5432> -U postgres> -d testdatabase> > /path/to/dump.dmp
The above command creates a plain text dump file at the location /path/to/dump.dmp. The command uses the default port on the remote server, which is 5432 unless otherwise specified by the user in the command line.
Using pg_dumpall creates a dump file for each database. The name of the dump file for each database will be in the format of:
dbname> is the name of the database
seq> is a sequence number for the database
An example pg_dumpall command is:
pg_dumpall -U -h -f /my/backup/dir/dbname>_seq> dumpfile.dmp
The pg_restore utility restores the contents of a dump file to a PostgreSQL server. The pg_restore utility does not require the database to be shut down while restoring the data. The syntax of pg_restore is as follows:
pg_restore [-v] [-f format] [-d directory] [-h hostname] dbname filename
If the filename is omitted, it is assumed to be the name of a PostgreSQL server. The options are as follows:
-U, --unlock-tables Unlock tables in the current schema locked by active transactions. This option unlocks those tables.
-f, --force-empty Forces pg_restore to restore empty tables and empty sequences even if they don't exist on disk.
-d, --directory Forces pg_restore to create files in a directory instead of restoring files into an existing directory hierarchy.
-i, --include=pattern Exclude specific files and directories from being restored. If no pattern is provided, all files and directories will be included in the restore operation.
> pg_restore –-v –-f csv –-d /home/postgres /home/postgres/data > pg_restore –-v –-f text –-d /home/postgres /home/postgres/data
The pg_restoreall command restores all the data from a backup file. The pg_restoreall command offers two restoration options.
The syntax of pg_restoreall is as follows:
pg_restoreall [-f format] [-v] dbname filename
> pg_restoreall -v -f csv /home/postgres/data/db1 > pg_restoreall -v -f text /home/postgres/data/db1
The pg_dump tool is invaluable to a data management strategy. From data recovery to audit compliance, pg_dump is the key to reliable data backup.
The tool is highly configurable to empower you to save and restore information at any level of granularity within your system.
If adding pg_dump to your data backup plan is on your roadmap, our team of data experts, can help. Get in touch.