pg_restore with connection limit 0

From: Олег Самойлов <splarv(at)ya(dot)ru>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: pg_restore with connection limit 0
Date: 2019-11-27 15:06:44
Message-ID: 39E384B7-2E9D-4A31-9151-CE592C6511A2@ya.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all.

I have task to refresh a test database from a production database (with masking) on the fly. To make masking we use pg_restore --create with three stages restoration. And one of the problem is daemons writing concurrently in the time of the restoration of a database. I need to block them in time of the restoration. ALLOW_CONNECTIONS=true don't work in this case, because it blocks a superuser too. We use CONNECTION LIMIT 0, because this blocks almost all, except a superuser. But to prevent a race condition we must set this in the same query as CREATE DATABASE, the postgresql syntax allow this. Now this is rather complex procedure:

We get `pg_restore --create --list −−section=pre−data` and `pg_restore ---stage --list −−section=pre−data`, diff them to get difference.
Get `pg_restore --create --use-list=diff −−section=pre−data` to get sql.
Edit sql: remove ALTER DATABASE ... CONNECTION LIMIT ... if exist, add CONNECTION LIMIT 0 to the CREATE DATABASE.
Send sql to the postgresql.
And so on.

Will be convenient to add CONNECTION LIMIT 0 to the CREATE DATABASE just by option of pg_restore.
And will be even more straight approach do not use CONNECTION LIMIT 0 is this case, but change ALLOW_CONNECTIONS to accept values: false, true, superuser. ("Superuser" to accept connections from superuser only).

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2019-11-27 15:13:59 Re: Range contains element filter not using index of the element column
Previous Message Joe Conway 2019-11-27 13:36:17 Re: Range contains element filter not using index of the element column