pg_restore parameters

On Friday 12 September 2008 14:23:52 Kevin Duffy wrote:

Hello:

I am move to a new production server and am testing my backup and
restore procedures.

Given a backup created with the follow command

C:\>C:\progFiles\PostgreSQL\8.2\bin\pg_dump -Fc -b -C -o -f
E:\backupPostgres\benchxx_c20080912.backup -U postgres benchxx_c

What is the best way to do a restore of the above backup? I tinkered
with a couple of scenarios.

The one the gave a clean restore was

a) drop the database

b) restore using the following: pg_restore -C -d template1 -U
postgres "E:\data\postgres\ benchxx_c20080912.backup"

Is this the correct way to do a restore?

Well, you need to add the -Fc flags on the restore at a minimum, but otherwise
TIAS.

Also, the use of -o is kind of a warning sign to possible bad schema design,
you shouldn't be using oids for anything, are you sure you need that flag? If
you do you might want to think about factoring that out of your design, if
not then verify you need the -b flag too.

As a final though, if you're already going through the pain of a dump/restore,
I'd suggest looking at upgrading to 8.3 during the process.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL