Skip to content

Backup/Restore PostgreSQL database

Backup and restore Postgresql database easy

Today a quick blog post about automatic PostgreSQL database backup. I will describe how easy and quick it is to automate database backups, just like it was with MySQL. If you have not read this article yet, I encourage you to find it at https://lepczynski.it/en/uncategorized/backup-restore-mysql-database-2/

Backup PostgreSQL database:

postgreSQL

1) Manual Backup PostgreSQL database:

At the beginning, let’s start with a manual backup. First, you need to install the postgresql-client on the computer that will be backing up. Once we have it, we define the connection parameters, we give the server’s IP address and port if it is non-standard. We will also need your username and password. In the example I used the user ‘admin’ and his password. At the end we save the backup in the indicated location, for me it is /mnt/cifs/backup_alldb_$DATA1.dump. The location contains the $ DATA1 variable which adds the current date to the file.

apt-get install postgresql-client
DATA1=$(date +"%Y%m%d")

# backup one database:
psql  -U admin -h 10.10.10.10 -p 30123 NAZWA_BAZY  > /mnt/cifs/backup_singledb_$DATA1.dump;

# backup all databases:
pg_dumpall -U admin -h 10.10.10.10 -p 30123 -o -v  > /mnt/cifs/backup_alldb_$DATA1.dump;

2) Automating Backup PostgreSQL database – Method 1:

Automating this process is simple, but we only started doing it after we checked that manual backup works. Such an automatic backup can be installed, for example, in the GitLab runner, which can perform a backup according to the scheduler. Here I am using the postgres: 11.6 image which is compatible with the backup server. Then the network drive is mounted, the backups will be saved on it. The backup itself looks almost identical to the manual operation. In addition, at the end I added a fragment that removes files older than 30 days. What I have not written here, and what else needs to be done is to define variables, logins and passwords and add a scheduler.

backup_postgres_X :
   stage: deploy
   image: postgres:11.6
   script:
     - DATA1=$(date +"%Y%m%d")
       # tworzenie folderów i montowanie dysków
     - mkdir -p /mnt/cifs
     - mount -t cifs -o username=$backup_user,password=$backup_pass //11.11.11.11/backup/db_postgres_nazwa1 /mnt/cifs
     - ls /mnt/cifs/
     
     # backup all databases
     - export PGPASSWORD=$postgrese_password
     - pg_dumpall -h 10.10.10.10 -o -U admin -p 30123 -v  > /mnt/cifs/backup_postgres_$DATA1.dump
     
     #delete file older then 30 days
     - find /mnt/cifs/ -maxdepth 1 -type f -mtime +30 -name 'backup_postgres*'
     - find /mnt/cifs/ -maxdepth 1 -type f -mtime +30 -name 'backup_postgres*' -exec rm {} \;
   only:
     refs:
       - schedules
   only:
     variables: 
       - $ACTION == "backup-postgres"

3) Automating Backup PostgreSQL database – Method 2:

Instead of creating Gitlab runners, you can backup databases on the postgresql server using cron. Just login as postgres and add the following command to the crontab (crontab -e):

0 2 * * * pg_dump -U postgres dbname > /mnt/cifs/db_backups/dbname-$(date +\%Y\%m\%d).dump
0 3 * * * find /mnt/cifs/db_backups/ -type f -name "*.dump" -mtime +30 -delete

And if we want to save some space, the files can be additionally packed:

0 2 * * * pg_dump -U postgres dbname > | gzip > /mnt/cifs/db_backups/dbname-$(date +\%Y\%m\%d).dump.gz
0 3 * * * find /mnt/cifs/db_backups/ -type f -name "*.dump.gz" -mtime +30 -delete

Restore PostgreSQL database:

Often useful in the event of a failure is the restore option to restore the database from a previously performed backup. The easiest way to do this is by using the command:

# lokalnie:
psql -U username -d dbname < filename.dump

# zdalnie:
psql  -U username -h 10.10.10.10 -p 30123 -d NAZWA_BAZY < /mnt/db/file.dump

Below are some simple extra commands for PostgreSQL, which are also sometimes useful to check if the database or user is on the server. Log in to the server locally or, for example, with ‘psql -h 10.10.10.10 -U admin –port = 30123 -d mydb

# database list:
\l

# delete database:
# DROP DATABASE database_name;
# displays tables from the database 'database_name':
\d database_name;
\dt;
# displays users and their roles:
\du
# quit:
\q

More information about postgresql itself can be found at https://www.postgresqltutorial.com/psql-commands/

If you want to know what automatic backup can look like in MySQL, please see my previous article on this topic:

Backup/Restore MySQL database
Tags: