How to lock an entire Postgres database

Question

I have a process (for backup and replication using binary logs) that works in MySQL that I want to replicate for Postgres databases (using WAL):

1. Lock database (FLUSH TABLES WITH READ LOCK;)
2. Disable binary logging (SET sql_log_bin = 0;)
3. Flush binary logs (FLUSH BINARY LOGS;)
4. Copy binary logs and apply to a remote database for replication purposes
5. Enable binary logging (SET sql_log_bin = 1;)
6. Unlock database (UNLOCK TABLES;)

This process works and I'm trying to do the same for Postgres. I was able to find that CHECKPOINT is what I need to flush the binary logs for Postgres but locking and unlocking the database isn't as straightforward as I hoped it would be.

Here's what I found so far:

• LOCK DATABASE is a database-level locking mechanism but is not implemented
• BEGIN; LOCK TABLE users IN ACCESS EXCLUSIVE MODE NOWAIT; seems to be able to lock the tables individually but apparently there is no UNLOCK TABLE command because locks are always released at transaction end
• default_transaction_read_only - I tried to look for the equivalent of FLUSH TABLES WITH READ LOCK; in Postgres and found this question

I tried the default_transaction_read_only solution and it does prevent new records from being added but I'm a little worried about what the answer in there said:

This takes effect for new sessions, not for sessions that are already connected.

Also when I tried to revert the read-only mode: BEGIN read write; ALTER DATABASE my_database SET default_transaction_read_only to OFF; it appears to have succeeded but when I try to add a new record, I get the following error:

ERROR: cannot execute INSERT in a read-only transaction

Any ideas on how I could perform the same process as I am doing for MySQL but for Postgres?

• Melkij: So, why you want the same process? None of this is needed in postgresql. Just pg_basebackup or more specific tools to manage pitr backups (pgbackrest, wal-g).
• dokgu: @Melkij we have a remote database meant for replication but it isn't always connected. So instead we have another layer of technology where we would ideally upload the WAL files to and when the remote does get connected, that extra layer of tech would sync it up to the remote and perform the replication there.
• Melkij: reasonable wal_keep_size, or replication slot with reasonable max_slot_wal_keep_size, or archive_command + restore_command + archive_cleanup_command. It depends on how long the replica may be disconnected.
• Melkij: There is no reason to lock the database for this. Well, if you really want to, you can even stop it. But this is not needed. In fact, there are only two points: somehow save the WAL that the replica has not yet received and somehow transfer them to the replica. archive_command (primary saves WAL file using this command) + restore_command (replica receives new WAL using this command) + archive_cleanup_command (the replica calls this command and indicates a position that is no longer needed for recovery) completely solve this task even if the databases have no direct connection at all.
• J.D.: @dokgu "I was just under the impression that it's generally a good idea to lock the database so that the primary is in a stable state and no new records etc are made until the maintenance is done. Isn't that a common practice?" - Nope.

Answer 1

Setting up a streaming replication standby server in PostgreSQL is much easier than that. Follow the documentation. MySQL is quite different from PostgreSQL when it comes to replication.

A simple setup could be as follows:

• on the primary server, create a user with LOGIN and REPLICATION, add an entry to pg_hba.conf that allows the standby server to connect with that user and reload the primary

• on the standby server, perform a base backup with

  pg_basebackup -h primary.server.com -U repuser -C -S slotname -D /data/directory -P
  

• on the standby, edit postgresql.conf and set primary_conninfo to a connection string for the primary and primary_slotname to the name of the replication slot you just created

• create an empty file called standby.signal in the standby's data directory

• start the standby server