7

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.

3

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.

  • Note that read-only transactions are only a default. An app can simply ask for a read/write transaction. Make sure your app won't do that. – Craig Ringer Sep 17 '17 at 9:18
  • I thought about this again. Does pg_dumpall dump this readonly state? This would that I need to set the database to write-mode again after I restore the dump on prod-server-new. Or do above settings not get dumped by pg_dumpall? – guettli Sep 18 '17 at 6:52
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.

2

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

?

  • Please elaborate "prevent remote log ons" – guettli Sep 20 '17 at 7:58
0

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('');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.