Postgresql – how to prevent multiple pg_dump to execute at the same time

backuppg-dumppostgresql

I am exposing a REST API to backup a Postgres DB. I have few questions related to that. I have seen most of these questions floating around google but could not find any suitable answer

Is there any way to know the status of the pg_dump? I can redirected the verbose log to the user but somehow I am not convinced with that.
Is there any way to know that a "pg_dump" session is in progress so that I can prevent the user from re-triggering?

Best Answer

You could check if there is running pg_dump process by looking at the pg_stat_activity and also the state of the process:

SELECT 
    pid,
    datname,
    usename,
    state
FROM pg_stat_activity 
WHERE 
    application_name = 'pg_dump';

It would be better if you could also lock some file with your script when you are starting pg_dump and check the lock status of that file when starting new pg_dump to avoid re-triggering.

Related Solutions

Google Cloud SQL – How to Set Up Vault and Postgres with Correct Permissions

Short Summary

You have to run an REASSIGN OWNED BY "$VAULT_USERNAME" TO "cloudsqlsuperuser" after the table/sequence/function creation process to reassign back to a the common role.

Longer Answer

Postgres 9.6 CREATE TABLE will by default create the table under the user's role. Because the vault user is designed to be a temporary credential, we need to also make sure that the objects created under the temporary roles are reassigned back to a common role of some kind. In the specific case I outlined in Google Cloud, we are going to reassign back to cloudsqlsuperuser.

The one command I found to work is the REASSIGN OWNED BY current_user to "cloudsqlsuperuser" command that will then take all of the objects owned by that temporary user and assign back to that common role.

Bonus: liquibase config

I am using liquibase to do my migrations and this is in my change log to fix the permissions everytime we run a migration:

<databaseChangeLog>
    <changeSet author="elindblom" id="fix-permissions" context="" logicalFilePath="fix-permissions" runAlways="true" runOrder="last">
        <sqlFile path="baselines/sql/fix-permissions.sql" splitStatements="false" relativeToChangelogFile="true" stripComments="false"/>
    </changeSet>
   ... rest of your change logs after ...
</databaseChangeLog>

The SQL looks like this:

REASSIGN OWNED BY current_user TO "cloudsqlsuperuser";

The changeSet runs after everything comes to completion and runs every-time. The very important bits are the runAlways and runOrder elements.

Postgresql – How to dump multiple databases of the same cluster in single execution

pg_dump can only dump a single DB. The manual:

pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall.

pg_dumpall, on the other hand can only dump all DBs in the cluster, even if you provide a specific DB name in the connection string. The manual:

-d connstr
--dbname=connstr

Specifies parameters used to connect to the server, as a connection string. See Section 34.1.1 for more information.

The option is called --dbname for consistency with other client applications, but because pg_dumpall needs to connect to many databases, the database name in the connection string will be ignored. Use the -l option to specify the name of the database used for the initial connection, which will dump global objects and discover what other databases should be dumped.

To dump some DBs but not all, use a shell script, like has been advised. You may want to include commands to create each DB in the dump:

How to get a pg_dump -s to include the CREATE DATABASE command?

Best Answer

Related Solutions

Google Cloud SQL – How to Set Up Vault and Postgres with Correct Permissions

Postgresql – How to dump multiple databases of the same cluster in single execution

Related Question