Re: pg_restore with connection limit 0
> 27 нояб. 2019 г., в 18:14, Adrian Klaver > написал(а): > Why not use pg_hba.conf to allow only connection from superuser for duration? We considered this, but we don't have direct access to the linux server, only sql connection. The application run in a kubernetes, etc. The most simple approach to block concurrent access to the database, may be, is run all pg_restore inside single transaction. But this is impossible with --create option, but --create option is the only way to restore database settings. And this is impossible for us, because we need pg_restore in 3 stages. It is inconvenient that the pg_restore has functionality for 3 stages, but don't has functionality to block database for time of this 3 stages.
Re: pg_restore with connection limit 0
On 11/27/19 7:06 AM, Олег Самойлов wrote:
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).
Why not use pg_hba.conf to allow only connection from superuser for
duration?
--
Adrian Klaver
adrian.kla...@aklaver.com
pg_restore with connection limit 0
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).
