pg_restore with connection limit 0
От | Олег Самойлов |
---|---|
Тема | pg_restore with connection limit 0 |
Дата | |
Msg-id | 39E384B7-2E9D-4A31-9151-CE592C6511A2@ya.ru обсуждение исходный текст |
Список | 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 therestoration of a database. I need to block them in time of the restoration. ALLOW_CONNECTIONS=true don't work in thiscase, 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 allowthis. 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 toget 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 acceptvalues: false, true, superuser. ("Superuser" to accept connections from superuser only).
В списке pgsql-general по дате отправления: