CODEWITHSUNDEEP
postgresql
Dr Rob Lang
Dr Rob Lang

Reputation: 6883

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

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.

Upvotes: 0

Views: 455

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 1

Related Questions