Backups are performed by a utility called ‘pg_dump’ while ‘psql’ can be used to restore from a backup file. Normally, people use the utility ‘pg_restore’, however, this utility requires the user to backup the database using a custom or tarball format. If you use plain text, which is the default format when using ‘pg_dump’, then you must use ‘psql’ to restore the database. The current database I’m working with is still quite small, weighing in at about 645 megabytes.
To backup the database I typed:
pg_dump -v -O --host=www.alexkuo.info --port=5432 --username=alex database_name > database_name.sql
The ‘-v’ flag turns on verbose mode and the -O flag skips any attempt to set a owner to the database. Other options that I tested were the -C flag for adding a create database statement and the ‘-c’ flag for adding drop table statements. I omitted the ‘-C’ and the ‘-c’ flags because I decided to restore into an existing database. Without the specification of file sizes and format, pg_dump will create one plain text file for the backup.
Some file systems have limitations on the maximum file size. NTFS file sizes are limited by the Max volume size of the drive – which is two terabytes, while FAT32 is limited to about four gigabytes. The EXT2 file system has a max file size of two terabytes. Max file sizes for Ext3 and Ext4 vary depending on block sizes, between 16 gigabytes to 2 terabytes. If needed, you can split files by piping into the split utility. The split utility is found only on Linux, Unix, and Cygwin.
To do this type:
pg_dump database_name | split -bytes=100m backup.bak.
This splits the plain text format of the backup into 100 megabyte pieces with the file named as ‘backup.bak.XXX’ where XXX is a digit.
If needed, you can append the backed up files when restoring by typing:
cat backup.bak. | psql -d database_name
If you’re restoring from a single plain text file, type:
psql -d database_name -f database_name.sql
If the database exists, errors will be thrown and the restore will fail – unless you specified the ‘-c’ flag in ‘pg_dump’ to add the ‘drop tables’ statements.
If you used the plain text format for your backup, creating an archive from the backup file can reduce the file size. For myself, zipping the backup file reduced the size from 695 megabytes to 60 megabytes.
For more information on file systems or backing up databases, please consult these links: