Backing Up the Database
This document attempts to provide information for both experienced and novice users alike. If the command line is your preference, we have command line examples for you to follow. There are also simple GUI approaches, as described in the pgAdmin section.
Command line examples (all platforms):
The following examples all use the user name "admin". This is the default name created when installing the PostBooks/OpenMFG software starting with version 3. Earlier versions used the user name "mfgadmin" as the default user and it is possible that the databases can be configured with a different name. You should use the appropriate privileged username in place of "admin" for your system.
If logged in to the server running Postgres as a user with the correct permissions, and Postgres runs on default port of 5432:
pg_dump -U admin databasename > backupname.sql
This will create a backup of databasename called backupname.sql
If Postgres is running on a port other than 5432:
pg_dump -U admin -p 5433 databasename > backupname.sql
If you are running pg_dump from a remote client:
pg_dump -U admin -h mypgserver.mydomain.com -p 5433 databasename > backupname.sql
See also documentation related to installing the database and restoring the database.
- Key Phrases:
The above described procedure will cause the pg_dump to dump the raw data without escaping single quotations in any text field in the data (I had e.g. a warehouse titled "John's warehouse"). This causes syntax errors throughout the data. The workaround for me was to use pgadmin -> Backup... -> Use the std setting but tick the box for "Insert statements". Pgadmin then automatically escapes all the single quotations correctly.
Another issue exists around the users in the db. The same users need to be in place on the old and the new system (for pb32quickstart that would be at least the users admin, mfgadmin and postgres). Otherwise the import fails. This is also discribed in the pgsql db manual.
Also, to my understanding you need to have super user rights on the db to be able to import the dump. (Grant all doesn't seem to do it.) Otherwise the attempt to set a language fails early on (like line 3 or so) due to insufficient permissions. This is what I received without super user permission:
ERROR: must be superuser to create procedural language
ERROR: syntax error at or near "PROCEDURAL" at character 7
LINE 1: ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres...
Hope that helps...
Cheers
Frank
If the above process doesn't work, what would be the correct procedure of Backup and Restore?
I currently run the Database on a MAC, so Time Machine seems to be taking care of backing up everything (I have tested Restoring it and it works!). But it backups all databases at once and can only restore all at once too. What if I only need to restore a single Database??
