Introduction

To take a backup in 2023, we've got a plethora of fancy softwares and utility tools. However, they all either lack the speed (yes, they do a lot of overhead tasks making them slow) or they are expensive. Eitherway, they cannot beat the cost and confort of a cmd line utility program that does its job at blazing fast speed and is free to use.

In this blogpost, we'll consider the command line utility pg_dump and some parameters we can use for specifying the table.

Taking Backup

PostgreSQL is an open-source and one of the most widely used relational database systems. It provides a program pg_dump to back up databases easily and swiftly. Please note here, pg_dump is a commandline utility program, not SQL statements.

To take backup, we just have to connect to the right database using correct credentials and to do so, we need to specify some parameters like username, table, host, port etc. To do so, open a terminal and navigate to the directory where we wish to store the dump and use this command,

pg_dump --host <HOSTNAME> 
        --port <PORT> 
        --dbname <DATABASE_NAME> 
        --username <USER_NAME> 
        --table <TABLE_NAME> [additional flags]  >   <FILE_NAME_TO_SAVE.sql>

As an example,

pg_dump --host localhost 
        --port 5432 
        --dbname bookstore
        --username postgres 
        --table books 
        --data-only 
        --column-inserts > bookstore_books_09_March_2022.sql

This above command will create a data dump taken from the books table from the database bookstore.

The flag --data-only (or -a in short form) specifies that only the data would be included in the data dump and schema information would not be included. This is desirable when we have already created the tables to store the data and we can issue the INSERT statements directly.

The flag --column-inserts (or -D in short form) specifies that the datadump shall contain only the INSERT statements which will come really handy when we need to either restore the data (say in case of a mishappening of data wipe) or to migrate data over to another database.

The datadump file would be created in the directory from where we issued the command.

We can also use the short forms like these

pg_dump -h localhost 
        -p 5432 
        -d bookstore
        -U postgres 
        -t books 
        -a
        -D > bookstore_books_09_March_2022.sql

Custom Schema DB export

If our database uses a non-public schema, meaning we specified the schema using a name which was something, other than public (if we didn’t specify any schema name, no worries, by default it will use public as the schema name), then we need to specify the schema using the --schema (or -n in short form) flag as shown in the command below.

pg_dump -h localhost 
        -p 5432 
        -d bookstore
        -U postgres 
        -t non_public_schema.books 
        -n non_public_schema
        -a 
        -D > bookstore_books_09_March_2022.sql

Here, the schema name is non_public_schema and if it is still not obvious, it is a custom schema, or something other than public schema. As we can see, the table name as been specified using two terms separated by a period. Whatever be the name of our schema, we can specify that custom schema name here as our first term. So the full table name would be schema_name.table_name.

Thats all we’ve to do to take the backup.

Restore Datadump

To restore or insert that data dump we created above which contains insert statements only, we can use the psql's flag -f to specify the filename that contains the datadump as shown in the cmd below,

psql -h <HOSTNAME> -p <PORT_NUMBER> -d <DATABASE_NAME> -U <USER_NAME> -f <DB_DUMP_FILE_NAME>

An example of this above command would be,

psql -h localhost -p 5432 -d bookstore -U postgres -f bookstore_books_09_March_2022.sql

Another way to restore the dump is to ue the redirect symbol < instead of using the -f flag to specify the datadump file, as shown in the command below

psql -h localhost -p 5432 -d bookstore -U postgres < database_name_20160527.sql

And thats how we can take backup and retore the data quickly using a free and open-source utility tool pg_dump. I hope this post helped you in some way.

Please reach out to me here in case something is still unclear or if you have any doubt. I'll try to respond as soon as I can. Until then, Keep coding.

Taking backup of Postgres Data using pg_dump

Introduction

Taking Backup

Custom Schema DB export

Restore Datadump

Tags