Create a database dump usable by cloud

1
On Windows using the tool pgAdmin III. I want to create a backup file that can be read from the cloud portal. When I right click on a database and click Backup. What options should I use, so I can upload and restore the database in the cloud? Format: Custom/Tar/Plain/Directory Compress ratio: Encoding: Rolename: Any extra options I should select? SOLUTION (see post below) Oke so I managed to create the backup with the following command: C:\Program Files (x86)\PostgreSQL\8.4\bin>pg_dump.exe -O -x -F c -U USER -W -p PORT DATABASE > FILELOCATION where: USER is a user that can logon to the database PORT is the port of the database server. I'm running my database on a cutom port, when you are using the default 5432 you can ignore the -p PORT part DATABASE is the name of the database you want to dump FILELOCATION is the location where you want to store the dump e.q. C:\dump-8.4.backup Please note that you have to use PostgreSQL version 8. I used postgresql-8.4.12-1. I first tried it with 9.1 but the dump and restore functions seem to not be compatible, because the Cloud uses 8. This might be something to add to the documentation. So next time somebody doesn't make the same mistake. After this I got an error in the cloud portal that stated that restoring the backup failed. After talking to Mendix I understood that I could ignore this error, and the application is working now, finally :)
asked
4 answers
1

There's some documentation for the Linux CLI, but I wouldn't know what the Windows equivalent would be.

answered
1

Oke, so the correct command (on Linux) is:

pg_dump -O -x -F c

Which you can read at the page Achiel linked to.

"-F c" tells me that the format should be custom.

I'm still trying to figure out how to set the "-O" and "-x" options.

-O

--no-owner

Do not output commands to set ownership of objects to match the original database. By default, pgdump issues ALTER OWNER or SET SESSION AUTHORIZATION statements to set ownership of created database objects. These statements will fail when the script is run unless it is started by a superuser (or the same user that owns all of the objects in the script). To make a script that can be restored by any user, but will give that user ownership of all the objects, specify -O. This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pgrestore.

-x

--no-privileges

--no-acl

Prevent dumping of access privileges (grant/revoke commands).

-F format --format=format Selects the format of the output. format can be one of the following: p plain

Output a plain-text SQL script file (the default).

c

custom

Output a custom archive suitable for input into pg_restore. This is the most flexible format in that it allows reordering of loading data as well as object definitions. This format is also compressed by default.

t

tar

Output a tar archive suitable for input into pg_restore. Using this archive format allows reordering and/or exclusion of database objects at the time the database is restored. It is also possible to limit which data is reloaded at restore time.

answered
1

Oke so I managed to create the backup with the following command:

C:\Program Files (x86)\PostgreSQL\8.4\bin>pg_dump.exe -O -x -F c -U USER -W -p PORT DATABASE > FILELOCATION

where:

USER is a user that can logon to the database

PORT is the port of the database server. I'm running my database on a cutom port, when you are using the default 5432 you can ignore the -p PORT part

DATABASE is the name of the database you want to dump FILELOCATION is the location where you want to store the dump e.q. C:\dump-8.4.backup

Please note that you have to use PostgreSQL version 8. I used postgresql-8.4.12-1. I first tried it with 9.1 but the dump and restore functions seem to not be compatible, because the Cloud uses 8. This might be something to add to the documentation. So next time somebody doesn't make the same mistake.

After this I got an error in the cloud portal that stated that restoring the backup failed. After talking to Mendix I understood that I could ignore this error, and the application is working now, finally :)

answered
0

For some reason above command did not work for me. I am using Postgres 8.4 on Windows.

Working command for me is:

c:\Program Files (x86)\PostgreSQL\8.4\bin>pg_dump -O -x -F c -U USERNAME -W -p 54 32 -f c:\FILENAME DATABASENAME

answered