Backups are important & they are our only contingency for when we delete things that we should not or there are malfunctions hardware or otherwise. Backups at regular intervals are essential for any organization to maintain a proper working environment. Same goes for database, database backups are extremely essential as it holds transactional, user data among other things.

In this tutorial, we will learn about PostgreSQL backup. Before we start this, you can also refer to our tutorials on installing Postgresql on CentOS/RHEL, also for installation on Ubuntu & installing Postgresql from the source.

Recommended Read: Important postgresql commands for beginners

Backup Postgres Database

Postgresql provides backup utilities when we install postgresql on the server, they are pg_dump & pg_dumpall. Pg_dump is most useful for taking postgresql backup of a single database, while pg_dumpall provides the postgresql backup for all databases on the server. Let’s see some examples.

Backup Postgres database, single DB

Start by switching to the user postgres,

# su – postgres

Now to back up a database, use the following command,

$ pg_dump -U user_name -d thelinuxgurus > thelinuxgurus.bak

Here, ‘thelinuxgurus’ is the name of the database we need to backup. We can also use .sql or .tar as extension to backup file.

Taking backup of one or more tables in postgres

Command to take the backup of a single table from a database is,

$ pg_dump -U User_name -d database_name -t public.users_table > users_table.bak

To take the backup of more tables,

$ pg_dump -U User_name -d database_name -t public.users_table public.test_table public.test_table_2 > 3_tables.bak

Restoring a database

Now if we need to restore the backup, we need to drop the old database & create an empty database,

$ dropdb thelinuxgurus

$ createdb new_db

Not to restore the backup, use the following command,

$ psql new_db < thelinuxgurus.bak

Or we can also use pg_restore utility to restore a database, use the following command to restore the database using pg_restore utility,

$ pg_restore -d db_name /path/to/your/file/thelinuxgurus.bak -c -U db_user

Restore one or more tables from backup

Similarly, we can also restore the backup of one or more tables,

$ pg_restore -U user_name –data-only -d target-db-name -t table_name /path/to/your/file/users_table.bak

Taking only schema backup

To take the backup of schema only, use the following command,

$ pg_dump –schema=schema_name db_name > schema.bak

Restoring schema backup

To restore schema backup, use the following command,

$ psql -d db_name -U user_name < schema.bak

Taking all database backup

The second backup utility i.e. pg_dumpall is used for taking backup of all the databases on postgreSQL. To take backup use the following command,

$ pg_dumpall > all_databases.bak

Restore all databases backup

To restore all database backup, use the following command,

$ psql -f all_databases.bak postgres

We can also use all or any of the above-mentioned commands & create a cron job for it to automate our backups. Please refer to our tutorial on Scheduling Cron jobs on Linux system. We now end this tutorial on backup Postgres database. Please feel free to send in any questions or queries using the comment box below.

If you think we have helped you or just want to support us, please consider these:-

Connect to us: Facebook | Twitter | Linkedin

TheLinuxGURUS are thankful for your continued support.