CODEWITHSUNDEEP
c#cpostgresqlpg-restore
Bagelstein
Bagelstein

Reputation: 202

Restore Multiple Schemas in a Single Transaction - Postgres

I have a rather large database with 5+ schemas. Currently I am creating backups by doing a pg_dump to generate individual .sql files for each schema. When I do a restore I am having to cycle through each schema and run a restore command individually which is time consuming and also presents issues if a particular schema were to fail. I could run into a situation where some schemas are updated while others following a failure may not be.

What I would like to do is run each of these restores in parallel as a single transaction so if one fails they all fail. Also, I know the easy answer to this would be not to backup as individual .sql files, unfortunately that is a requirement I cannot change.

Also I should note I am launching off these postgresql commands from a c# baseline. An additional option I explored was to launch new threads for each .sql file in the restore, this doesnt solve my single transaction problem though.

Upvotes: 1

Views: 2487

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61656

What I would like to do is run each of these restores in parallel as a single transaction so if one fails they all fail

SQL statements belonging to the same transaction cannot run in parallel.

To get an all-or-nothing mode of failure, you may just start a single transaction, run all the schema restores in that transaction, and commit once at the end.

For instance, if you have 3 schemas obtained by

pg_dump -n schema1 dbname >s1.sql
pg_dump -n schema2 dbname >s2.sql
pg_dump -n schema3 dbname >s3.sql

This could be restored in psql, or the equivalent in your C# code, with:

\set ON_ERROR_STOP on
BEGIN;  -- start transaction
\i s1.sql -- run all commands in s1.sql
\i s2.sql -- run all commands in s1.sql
\i s3.sql -- run all commands in s1.sql
COMMIT;

This sequence will succeed or fail as a whole. It is possible because the dump files obtained above don't contain any transaction control command. For example, for an empty schema it will be:

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: schema1; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA schema1;


ALTER SCHEMA schema1 OWNER TO postgres;

--
-- PostgreSQL database dump complete
--

Upvotes: 2

Related Questions