How do I restore a Postgres database from within a SQL query?

Question

When in development, I need to clean down my system by restoring a database backup and then running SQL against it. Is there a way to do this:

restore schema dbo 'c:/data/mydatabase.backup';
update dbo."MyTable" set "ColumnA" = 1;

Unlike other questions, this is not for doing via the command line but in a query instead.

Postgres v9.5 with pgAdminIII.

No, there is no such SQL statement. The only way is to do it via command line (either with psql or pg_restore) — user330315
– user330315, Commented Nov 25, 2016 at 14:47
Thank you, would you care to make that an answer? So I can close off the question for anyone else landing here. — Dr Rob Lang
– Dr Rob Lang, Commented Nov 25, 2016 at 14:50

user330315 · Accepted Answer · 2016-11-25 15:23:24Z

1

There is no support to restore a dump from within a SQL statement in Postgres.

Some possible (ugly) workarounds:

If your dump is a SQL script you could theoretically run that script from wherever you would run that (hypothetical) restore statement - but that would require parsing the script. And you would need to generate the dump using the --inserts option as otherwise the script contains psql specific statements.

Another workaround could be to create a stored function in a un-trusted language that is able to run operating system commands. The actual restore would still be done by the command line utility, but you could initiate that from within SQL.

answered Nov 25, 2016 at 15:23

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Dr Rob Lang Over a year ago

Thanks for the ugly workarounds. I'm going to go into Powershell instead!

Collectives™ on Stack Overflow

How do I restore a Postgres database from within a SQL query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related