Backing Up the Database
To prevent data loss, you are advised to back up the database data before uninstalling the database.
Basic Commands
pg_dump is a tool used to back up the PostgreSQL database. It makes consistent backups even if the database is being used concurrently and does not block other users from accessing the database.
The dump files generated by the tool can be in either of the following formats:
- Script format: It is a plain-text format that contains many SQL commands. These SQL commands can be used to rebuild the database and restore it to the state when the script is generated. This operation needs to be performed using psql.
- Archive format: The archive format must be used together with the pg_restore tool to rebuild a database. During the rebuild process, you can select the objects to be restored, or even sort the items to be restored before the restoration.
This command is used as follows:
1 2 | pg_dump [option...] [dbname] pg_dump -h <host> -p <port> -U<username> -W<password>-d <database> -f /path/file name.sql |
Parameter |
Description |
|---|---|
-h |
Specifies the IP address of the database server connected to the PostgreSQL database. |
-p |
Specifies the data port to be connected. |
-U |
Specifies the database user. |
-W |
Specifies the database password. |
-d |
Specifies the database name. If the database name is not provided, the value of the PGDATABASE environment variable is used. |
-f |
Specifies the file name. |
-F |
Specifies the dump format. The default is the script format. |
After the backup process is complete, you can use psql to restore data.
Example
- The path should be replaced with the actual database installation path. In this section, /usr/local/pgsql is used as an example.
- The database name should be replaced with the name of the actual database to be backed up. In this section, postgres is used as an example.
- Export only the data of the postgres database (-s is used to exclude data schemas). Run the following command as user root.
1/usr/local/pgsql/bin/pg_dump -U postgres -f /postgres.sql -s postgres
- Export the postgres database (including data). Run the following command as user root.
1/usr/local/pgsql/bin/pg_dump -U postgres -f /postgres.sql postgres
- Export data from the table01 table in the postgres database.
Create a table01 table. Run the following command after logging in to the database:
1postgres=create table "table01" ("Name" varchar(255));
Run the following command as user root.
/usr/local/pgsql/bin/pg_dump -U postgres -f /postgres.sql -t table01 postgres
- Export data from the table01 table in the postgres database and save the data using the insert statement.
1/usr/local/pgsql/bin/pg_dump -U postgres -f /postgres.sql -t table01 --column-inserts postgres
- Restore data to the bk01 database. bk01 is a created database.
1/usr/local/pgsql/bin/psql -U postgres -f /postgres.sql bk01