Skip to main content

Posts

Showing posts from March, 2022

Backup and Restore PostgreSQL Database

PostgreSQL has been shipped with a lot of tools for ease of managing the database. Some actions that we typically perform in managing databases are backing up a database and restoring it to another database server. In PostgreSQL, we can utilize pg_dump to back up the data from a database and psql or pg_restore to restore the data into a new database. Generating A Backup File There are several available formats for the exported data: plain, directory, compressed, and custom. The plain format is using plain SQL syntax to export the data. To make the tables are exported separately, we can utilize directory format. The custom format is utilizing a built-in compression mechanism and results in an optimized binary formatted file. It is suitable for a large database. If we use a custom format, we can only restore it using pg_restore , but we have the ability to selectively choose desired tables to be restored. The following command is used to generate a plain formatted file with th