Postgres ******** .. highlight:: bash .. note:: In these examples, ``green`` is the live database. .. note:: In these examples, the owner name is the same as the database name. List current databases:: psql -U postgres -c "\l" Backup database:: pg_dump -U postgres green -f ~/repo/temp/out.sql Restore data (from ``green`` to the empty ``blue`` database):: psql -U blue -d blue -f out.sql 2> out.log .. important:: Remember to check ``out.log``. Authentication ============== For more information ref `md5` and `scram-sha-256` see our `Chat ref scram-sha-256`_ and `Ticket 1437`_. Logging / Journal ================= - :ref:`sys-systemd-journal-configuration` .. _sys-postgres-logging-sql: SQL Logging ----------- To log SQL queries and their parameters:: # vim /etc/postgresql/14/main/postgresql.conf log_statement = all # restart postgresql tail -f /var/log/postgresql/postgresql-14-main.log .. tip:: The parameters appear **after** the query in the logs e.g: :: 2023-12-07 10:26:23 GMT LOG: execute : SELECT RES.* from ACT_RE_PROCDEF RES WHERE RES.ID_ in ($1) order by RES.ID_ asc 2023-12-07 10:26:23 GMT DETAIL: parameters: $1 = 'createInvoice:4:7bd4c336-80ae-11ee-b09f-000d3a7f022c' .. warning:: If the query fails, the parameters are not written to the log file (which is not very helpful). For more information, see :ref:`sys-flowable-value-too-long` .. _sys-postgres-microsoft-azure: Microsoft Azure Database for PostgreSQL ======================================= - :doc:`sys-azure` Microsoft recommend using PgBouncer_ with Azure Database for PostgreSQL: `Setup PgBouncer connection pooling proxy with Azure DB for PostgreSQL`_ The configuration below assumes you will be using PgBouncer. The Microsoft Azure Database for PostgreSQL has an extra layer of security (authentication). To configure this: Create a ``db/postgres_settings.sls`` file in your pillar e.g: .. code-block:: yaml # db/postgres_settings.sls postgres_settings: bouncer: True bouncer_listen_on_all_addresses: True listen_address: mypgserver.postgres.database.azure.com postgres_pass: azure-postgres-db-password postgres_ssh: verify-full postgres_user: azure-postgres-db-user postgres_host: mypgserver And add it to the config for the server e.g: .. code-block:: yaml # top.sls 'test-a': - db.postgres_settings - sites.my Add the ``db_user`` to the ``sites`` pillar e.g: .. code-block:: yaml # sites/my.sls sites: www.hatherleigh.info: package: hatherleigh_info profile: django db_user: "www_hatherleigh_info@mypgserver" In your Django project, ``settings/production.py``:: DOMAIN = get_env_variable("DOMAIN") DATABASE_NAME = DOMAIN.replace(".", "_").replace("-", "_") DATABASES = { "default": { "ENGINE": "django.db.backends.postgresql_psycopg2", "NAME": DATABASE_NAME, "USER": get_env_variable("DB_USER"), "PASSWORD": "", "HOST": "localhost", "PORT": "6432", "DISABLE_SERVER_SIDE_CURSORS": True, } } .. warning:: Don't forget to ``DISABLE_SERVER_SIDE_CURSORS``. Read Only User ============== Add ``database_users`` to your pillar file. The key is the name of the read only user e.g. ``bi_www_hatherleigh_info``: .. code-block:: yaml # sites/my.sls sites: www.hatherleigh.info: db_pass: MyPassword db_type: psql database_users: bi_www_hatherleigh_info: pass: MyReadOnlyPassword env: activiti_host: "localhost" activiti_pass: "kermit" The Salt state will create the read only users in the PGBouncer files. .. tip:: Prefix the name of the read only user with ``bi_`` (business information / reporting). Use ``fabric`` to create the read only users e.g:: fab create-database-users www.hatherleigh.info .. warning:: The user will **not** have access to database tables created after running this command. To give the user access to new tables, just run the command again. .. _postgres-upgrade-cluster: Upgrade Cluster =============== After a ``do-release-upgrade``, you will probably be left with several database clusters e.g:: # pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 9.3 main 5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log 9.5 main 5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log 10 main 5434 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log .. tip:: Example commands for moving from Postgres 10 to 12, can be found here: :ref:`ubuntu-postgres-upgrade-cluster`... To move the data from Postgres 12 to Postgres 14, run the following as ``root``:: # list the clusters pg_lsclusters # drop the new (empty) cluster pg_dropcluster 14 main --stop # migrate the data from 12 pg_upgradecluster 12 main # remove the 12 cluster pg_dropcluster 12 main --stop # remove the old database engines apt remove postgresql-12 # list the clusters pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log .. _`Chat ref scram-sha-256`: https://chat.kbsoftware.co.uk/kb/pl/zfri6earujrb8m6sy3y1wqgs6r .. _`Setup PgBouncer connection pooling proxy with Azure DB for PostgreSQL`: https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/steps-to-install-and-setup-pgbouncer-connection-pooling-proxy/ba-p/730555 .. _`Ticket 1437`: https://www.kbsoftware.co.uk/crm/ticket/1437/ .. _PgBouncer: https://www.pgbouncer.org/