1

I need to run code when a database/catalog in a Postgres cluster comes online, before any client has connected.

Specifically I need to do some cleanup such as dropping/truncating a particular table.

Is there a place for my code to be automatically run with a guarantee it will finish before any incoming client connection is accepted?

Improve this question
4
  • Which operating system are you using? On a Linux based system you could probably just edit the startup script. – a_horse_with_no_name Jun 20 '17 at 6:23
  • @a_horse_with_no_name Currently I use macOS, but deployment may eventually involve any platform. To what startup script do you refer? – Basil Bourque Jun 20 '17 at 8:04
  • Depends on what "init" system you are using. service or systemctl are essentially wrappers around a shell script (e.g. /etc/init.d/postgresql-9.6) Although I don't know if that shell script gets overwritten if you install any updates using the repository manager (apt-get, yum) - I am not a Linux guy – a_horse_with_no_name Jun 20 '17 at 8:11
  • As an aside, the function pg_postmaster_start_time stores the servers last start time if you find that useful. you could perhaps store it in a COMMENT on the table (table meta-data). – I Support The Boycott Jun 20 '17 at 16:07
3

You have two methods of doing this,

  1. Step back, your best bet is to use TEMPORARY TABLES

    TEMPORARY or TEMP If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

    The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands. For example, if a temporary table is going to be used in complex queries, it is wise to run ANALYZE on the temporary table after it is populated.

    Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes no difference in PostgreSQL and is deprecated; see Compatibility.

  2. Alternatively, you could rig it into your daemon that starts the server by,

    1. Creating two pg_hba.confs,
      • One that denies external connections.
      • One that permits external conections.
    2. Create two config files, one to each pg_hba.conf
    3. Start the server with the config file that points to the pg_hba.conf that denies external connections.
    4. Run the code you want.
    5. Stop the server.
    6. Run the server with the config file that points to the pg_hba.conf that permits external connections.

You could do a similar thing with postgresql.conf's listen_addresses parameter.

Improve this answer
3
  • 1
    Temp tables is a good suggestion, except that in my case the table needs to be non-temporary to be shared by all the users. Examples of this need are (a) a pessimistic lock tracking table, (b) an audit trail that should be dumped and then start fresh on every restart of server. – Basil Bourque Jun 20 '17 at 2:48
  • @BasilBourque I don't see how the server start or stop should have anything to do with that: MVCC ensures the transactions are atomic. It would seem like you're likely to have consistency issues in either of those use cases. – I Support The Boycott Jun 20 '17 at 2:50
  • 1
    MVCC and transactions in Postgres make supporting a pessimistic locking system both inside and outside of transactions impossible; see the Advisory Locks documentation. The start or stop of the app is not directly related, db start/stop is simply be a good time, a ripe opportunity, to clear the system of any remaining lock records if doing pessimistic locking as a table, or to dump an audit trail and restart if that were your desired security policy. – Basil Bourque Jun 20 '17 at 2:54
2

The simple way to ensure no other connections would be to start the server in single-user mode. Run your commands, then terminate the session with Control+D (typically) and restart the server in your standard operation mode. You can wrap all of this into a shell script to replace plain server start ...
Consider instructions in the manual.

Or you could start the server with -N 1. The manual:

-N max-connections

Sets the maximum number of client connections that this server will accept. The default value of this parameter is chosen automatically by initdb. Specifying this option is equivalent to setting the max_connections configuration parameter.

With superuser_reserved_connections at least 1 (default is 3), only a superuser can connect. You still need to restart the server (without this setting) when done because, the manual again:

This parameter can only be set at server start.

Related:

Aside: while a temp table seems to be no option for you, an UNLOGGED table might be. Visible to all sessions, but faster than a plain table. Since you truncate the table at server start anyway, the risk of data loss after a server crash seems irrelevant to the use case.

Improve this answer
1
  • I believe single-user mode is mainly made for initdb, and discouraged for user-activity. The superuser_reserved_connections / max-connections is probably the best idea. – I Support The Boycott Jun 20 '17 at 15:53
0

Since you mentioned restart, I'm assuming a disruption is ok! So maybe manipulate the permissions? or even the database name ...

You could do something like revoke permissions from the roles, kill connections, run code, re-grant permissions...

Improve this answer

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.