QNAP/Linux Tool - PostgreSQL Database Backup and Restore

PostgreSQL support installations on major OS platforms: Windows, Mac and Unix. That is I can backup my databases/services running on QNAP to my Windows machines. First, install PostgreSQL 9.4 (the newest version) on Windows 8.1.

PostgreSQL 9.4 program group in Window 8.1 MetroUI

First, add a new server connection to QNAP/PostgreSQL and login with admin account "postgres".

Add a dbserver connection

The PostgresSQL database can be backup with "New Database" by selecting the source as the "template".

Input dbname: bak

Select Template with the source db: fbi

Press "OK" to run CREATE DATABASE process, the database "bak" was created instantly. Check the database size with SQL query, both are the same.

select pg_database_size('fbi'), pg_database_size('bak');

Check dbsize

The output of pg_database_size() can be formatted with pg_size_pretty().

select datname AS Name, pg_size_pretty(pg_database_size(datname)) as Size
from pg_database
order by pg_database_size(datname) desc;

Original databases within QNAP's postgres

Original databases within Windows postgres

NOTE: pg_database_size() includes the sizes for indexes.

Database Backup operation can be done from the pgAdminIII UI.

Right-click on the source database to select "Backup" and type the backup file path+name

Backup process is running with pg_dump messages

As you can get command and message information from the dialog box. So that writing the command into BAT file and setting the Windows task schedule (or Linux crontab) to running
C:/Program Files/PostgreSQL/9.4/bin\pg_dump.exe --host 192.168.0.11 --port 5432 --username "postgres" --no-password  --format custom --blobs --verbose --file "D:\fbi.backup" "fbi"

Similar operation is applied to Database Restore, the command is also obtained from the dialog.

Create an empty database, right-click on the database to select "Restore", and type the file

Restore process is running with pg_restore messages

C:/Program Files/PostgreSQL/9.4/bin\pg_restore.exe --host localhost --port 5433 --username "postgres" --dbname "bak" --no-password  --verbose "D:\fbi_110.backup"