News
Happy System Administrator Appreciation Day - to the true heroes of IT!
Serverspace Black Friday
RF
September 29 2023
Updated July 31 2025

How to Backup and Restore Data in PostgreSQL: Step-by-Step Guide

Databases Linux

Introduction

The modern information system have many requirements for effectiveness and stability work. One of this fault tolerance, which provide protect from attack on integrity and availability, technical issue and another different incident, that can corrupt sufficiently data. For using tools and utilities we need to determine area of usage, in that instruction we will consider reservation and backup data in DBMS PostgreSQL through the proprietary and open-source utility.

Best practice

We can have database with different size, structure and volume, but we need to make full integrity backup and restore that properly. First utility that we can use it's pg_dump and psql or pg_restore. It use mechanism of read currently databases and convert to the command in the text file, therefore for input at the utility we indicate DB, for output we get text file.

That method have several advantages: version consistency, simplicity on export step and there isn't requirement for privilege rights in the system. For use that command type of backup, first of all we need to check there are databases and choose needed object:

\l
List of DB
Screenshot №1 — List of DB

Let's make sure, how does the data look like? Type command below:

SELECT * FROM customers;
Content
Screenshot №2 — Content

We have test record in the DB with theoretical user's data, we use pg_dump utility with option: -U for determine user of DBMS, -d identify database and for remote administration we can use -h and -p parameters. Host and port accordance:

pg_dump -U postgres -d db12 > backup.sql
Backup
Screenshot №3 — Backup

Also we can use sequences of command for completing that task:

ssh user@hostname -p 5432  pg_dump -U postgres -d db12 > backup.sql

Replace username, hostname and port for your desirable configuration. If you want to backup and restore DB rapidly between several server, then enter that:

pg_dump -h 78.43.11.2 db_name | psql -h 72.43.11.2 db_name

First part allowed connect to the server with db and by using pipe redirect output to the target server with command after separated sign.

And using command scp we can transfer db:

scp ./backup.sql root@94.141.98.9:/
SCP
Screenshot №4 — SCP

We should create database and restore content by the command below:

CREATE DATABASE db12;

Then restore with psql command:

psql -U postgres -d db12 < backup.sql
Restore
Screenshot №5 — Restore

Make sure integrity of content in the DB:

SELECT * FROM customers;
Result
Screenshot №6 — Result

If database have a lot of data and great size, use split and zip-utility for transfer pices of data:

pg_dump name_db | gzip > name_archive.gz
gunzip -c name_archive.gz | psql name_db

And split with syntax below:

pg_dump name_db | split -b 4G - name_file
cat name_file | psql name_db

That help for more effectiveness transfer data in process of backup.

Conclusion

Following these best practices and using PostgreSQL’s built-in tools like pg_dump, psql, and pg_restore enables you to reliably backup and restore your databases. Whether you’re working with small or large datasets, these methods ensure data integrity, version consistency, and smooth recovery in case of failures or data corruption. Proper backup and restore strategies are essential for maintaining the stability and availability of your PostgreSQL-based information systems.

FAQ

  • Q: What are the common tools for backing up PostgreSQL databases?
    A: The most widely used tools are pg_dump for creating backups and psql or pg_restore for restoring data. These utilities support exporting data as plain SQL files or custom archive formats.
  • Q: Can I backup a remote PostgreSQL database?
    A: Yes, you can use pg_dump with the -h (host) and -p (port) options to connect to a remote database and perform backups over the network.
  • Q: How do I restore a large PostgreSQL backup efficiently?
    A: For large databases, it’s recommended to compress backups using tools like gzip and split them into smaller chunks for easier transfer and faster restoration.
  • Q: Is it necessary to stop the database server during backup?
    A: No, pg_dump creates consistent backups without stopping the server, allowing continuous database operation during the backup process.
  • Q: How often should I perform backups?
    A: Backup frequency depends on your data change rate and recovery objectives, but regular backups (daily or weekly) combined with transaction log archiving are recommended for critical systems.
Vote:
5 out of 5
Аverage rating : 5
Rated by: 1
1101 CT Amsterdam The Netherlands, Herikerbergweg 292
+31 20 262-58-98
700 300
ITGLOBAL.COM NL
700 300

You might also like...

We use cookies to make your experience on the Serverspace better. By continuing to browse our website, you agree to our
Use of Cookies and Privacy Policy.