THIS IMAGE NOT IN USE

Was created to provide upstream image w/ cron extension pre-installed. Problem with this is tho that this extension can only be created in a single database, thus limits number of apps that can access it, or at least makes the coordination more complex. For split-pro app we went with separate postgres instance instead.

Also note there's no real need for the init script, as the extension can simply be installed from the psql command line (i.e. same command we run from the included init script), and afterwards the create statement will be included in the db dump. So the only real value provided by this image would be the apt-installed postgresql-<ver>-cron package.

---

Provides an upstream postgres image with cron extension preinstalled

For the upstream Dockerfile, see here

installing pg_cron

• see https://github.com/citusdata/pg_cron#installing-pg_cron
• tl;dr: see Dockerfile

Note

For the initial startup, make sure to add required options to cmd, as by this point we haven't yet restored postgres.conf file that includes these keys: postgres -c shared_preload_libraries=pg_cron -c cron.database_name=postgres

If not, the init script will likely error, but as database is initialized, then on the subsequent restarts the init scripts no longer run, as described in our init file header.

upgrading major pgres versions

• option 1: use pg_upgrade

• option 2: via pg_dumpall docs

  • supposedly safer

• note docs mention:

  Though you can upgrade from one major version to another without upgrading to intervening versions, you should read the major release notes of all intervening versions

  Does that imply we can upgrade from say v15->18, or does it mean no need to do the intervening minor versions?

pg_dumpall

• recommended to use pg_dump* programs from the newer version, but so far I've been ignoring it and dumping w/ the pg_dump from old container.

1. either stop all services using db, or disable their access (or do both!):
   • nvim /mnt/user/appdata/postgres/pg_hba.conf
   • sroll to the bottom
   • change all entries' METHOD value from trust to reject that do not start with local
     • likely just changing the last line host all all all scram-sha-256 to host all all all reject should suffice, as long as it's the only rule that applies to non-local connections
2. restart container/server
3. ssh/dbash to our pgres container
4. run pg_dumpall -U postgres > "/config/dumpall-$(date '+%Y-%m-%d_%R')"; echo $?
5. stop the server/container
6. on host, rename our postgres dir: mv /mnt/user/appdata/postgres /mnt/user/appdata/postgres.old
7. upgrade image to next version
8. start the container/service
9. restore pg_hba.conf & postgresql.conf
   • optionally diff them from host beforehand:
     • vimdiff /mnt/user/appdata/postgres.old/postgresql.conf /mnt/user/appdata/postgres/postgresql.conf
     • vimdiff /mnt/user/appdata/postgres.old/pg_hba.conf /mnt/user/appdata/postgres/pg_hba.conf
       • make sure not to revert access restriction yet!
   • cp /mnt/user/appdata/postgres.old/{postgresql.conf,pg_hba.conf} /mnt/user/appdata/postgres/
10. restart the container
11. on host, move dump to new dir: cp /mnt/user/appdata/postgres.old/dumpall-* /mnt/user/appdata/postgres/
12. restore data: psql -d postgres -U postgres -f /config/dumpall-???; echo $?
13. revert our modifications to pg_hba.conf
14. restart
15. nuke dump: rm /mnt/user/appdata/postgres/dumpall-*
16. if all good, nuke our old backup: rm -r /mnt/user/appdata/postgres.old

pg_upgrade

TODO