[GENERAL] pg_dump restore as transaction?

[Owen Hartnett]

If my perusal of the sql generated by pg_dump is correct, then it 
doesn't appear that it's wrapped in a transaction, and thus might be 
able to only complete a partial restore?


Or does

psql myDatabase mypg_dumpfile

wrap the file stream in a transaction?

If not, is there a reason why it can't be done so (some process that 
cannot be run as a transaction inside the file)?


Or should I just add begin and commit statements at the beginning and 
end of file?


I want to provide a mechanized daily update of one schema into a 
differently named database, and I'd like it to rollback if if fails.


-Owen

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump restore as transaction?

[Tom Lane]

Owen Hartnett [EMAIL PROTECTED] writes:
 If my perusal of the sql generated by pg_dump is correct, then it 
 doesn't appear that it's wrapped in a transaction, and thus might be 
 able to only complete a partial restore?

That's correct, and intentional.  You can use pg_restore's -1 switch
or add begin/end manually if you don't want it to work that way.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump restore as transaction?

[Alvaro Herrera]

Owen Hartnett wrote:

 If my perusal of the sql generated by pg_dump is correct, then it  
 doesn't appear that it's wrapped in a transaction, and thus might be  
 able to only complete a partial restore?

You're right, it is not.  Try pg_restore --single-transaction.  (You'll
need pg_dump -Fc though.)


-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump restore as transaction?

[Peter Billen]

Quoting Owen Hartnett [EMAIL PROTECTED]:



If my perusal of the sql generated by pg_dump is correct, then it 
doesn't appear that it's wrapped in a transaction, and thus might be 
able to only complete a partial restore?


Or does

psql myDatabase mypg_dumpfile



Try to use pg_restore with the following option:

 -1, --single-transaction
  restore as a single transaction

Or psql with the following option:

 -1 (one)  execute command file as a single transaction

Kind regards,

Peter



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_dump restore as transaction?

[Owen Hartnett]

At 12:37 PM -0500 12/1/08, Tom Lane wrote:

Owen Hartnett [EMAIL PROTECTED] writes:

 If my perusal of the sql generated by pg_dump is correct, then it
 doesn't appear that it's wrapped in a transaction, and thus might be
 able to only complete a partial restore?


That's correct, and intentional.  You can use pg_restore's -1 switch
or add begin/end manually if you don't want it to work that way.

regards, tom lane


Thanks to everybody for their help.  You guys are great.

-Owen

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general