How do I prevent changes to my PostgreSQL database?

Question

My steps:

1. run the final pg_dumpall on a server prod-server-old
2. Shut down prod-server-old for ever.
3. copy the output to a different server (prod-server-new)
4. Restore the db there.
5. All traffic goes to prod-server-new now

How can I avoid modification to the database during pg_dumpall (between step1 and step2), so that modifications during this time cannot be lost?

In my case it is 100% OK to have a small down time.

There are several databases in the "cluster" (btw, I do not like the word "cluster". I guess most people think a "cluster" is a group of several computers, but in this case it means one Postgres server, which holds several databases).

My question was marked as possible duplicate to "Make Postgres database temporarily read-only (for performing volume snapshots)". I don't think it is a duplicate, since in my case is different since I don't ask for a temporary read-only state.

Answer 1

Two ways to do that:

• If you don't mind about downtime (easy way):

revoke the connect privilege to the public group. That will prevent everyone but the superusers to connect. Then restart the server or terminate all connections, then proceed with the backup using a superuser account.

REVOKE CONNECT TO DATABASE (database) FROM public

• If you do mind about downtime:

Put your database in read-only mode:

ALTER DATABASE (database) SET default_transaction_read_only = true;

Proceed with pg_dumpall. No restart required and no downtime.

Of course, you have to repeat this on every database you want to "lock". If you have a significative number of databases, you can make the entire cluster read-only: set default_transaction_read_only = on; in your postgresql.conf then reload the postgres service.

Please note that I don't have tested any of these commands, proceed at your own risk.

Answer 2

One work-around:

Change the port of PostgreSQL on prod-server-old. This way it is unlikely that clients connect to the DB during pg_dumpall --port=OTHER_PORT.

Answer 3

I am a SQL Server admin (not Postgres) so I'm not 100% sure, but if downtime is not an issue can you:

• kill all connections
• prevent remote log ons
• log on to the db server and log into the db as super user
• do the pg_dumpall
• re-enable remote log ons

?

Answer 4

Using triggers:

create function __fn_db_readonly() returns event_trigger language plpgsql as $$
begin
  raise exception '%', 'Database is in read-only mode';
end $$;

create function _fn_db_readonly() returns trigger language plpgsql as $$
begin
  raise exception '%', TG_ARGV[0];
end $$;

create function set_db_readonly(amessage text) returns void language plpgsql as $$
declare
  t record;
  tbl_name text;
  stm text;
begin
  if coalesce(amessage, '') = '' then
    -- Database
    drop event trigger __tg_ro_database1;
    drop event trigger __tg_ro_database2;
    drop event trigger __tg_ro_database3;
    -- Data
    for t in (select * from pg_tables where not schemaname in ('pg_catalog', 'information_schema')) loop
      tbl_name := format('%I.%I', t.schemaname, t.tablename);
      stm := 'drop trigger if exists _tg_ro on ' || tbl_name;
      execute stm;
    end loop;
  else
    -- Data
    for t in (select * from pg_tables where not schemaname in ('pg_catalog', 'information_schema')) loop
      tbl_name := format('%I.%I', t.schemaname, t.tablename);
      stm := format('create trigger _tg_ro before insert or update or delete on %s execute procedure _fn_db_readonly(%L)', tbl_name, amessage);
      execute stm;
    end loop;
    -- Database
    create event trigger __tg_ro_database1 on ddl_command_start execute procedure __fn_db_readonly();
    create event trigger __tg_ro_database2 on table_rewrite execute procedure __fn_db_readonly();
    create event trigger __tg_ro_database3 on sql_drop execute procedure __fn_db_readonly();
  end if;
  return;
end $$;

Then to make database read-only:

select set_db_readonly('Database is in read-only mode due to maintance.');

and to make it editable again:

select set_db_readonly('');