Operations grimoire/PostgreSQL - Revision history

Dereckson: /* Upgrade */

2024-08-17T12:20:19Z

Upgrade

← Older revision		Revision as of 12:20, 17 August 2024
Line 107:		Line 107:
	When upgrading PostgreSQL the following checks must be done:		When upgrading PostgreSQL the following checks must be done:

	* Do we have the postgres -contrib port installed too for the pg_trgm module?		* Do we have the postgres -contrib port installed too for the pg_trgm module for FANTOIR?
	* Do we have XML support for ~~FANTOIR~~?		* Do we have XML support for Orbeon?

	There are several methods to upgrade a server:		There are several methods to upgrade a server:

2024-08-17T12:18:38Z

Upgrade notes

@@ Line 102: / Line 102: @@
 | A || Airflow > airflow_scheduler || On Dwellers, docker restart airflow_scheduler
 |}
 == Clusters ==

2024-06-20T19:50:22Z

← Older revision		Revision as of 19:50, 20 June 2024
Line 84:		Line 84:
	Sequences aren't included in that GRANT ALL PRIVILEGES. We can generate GRANT queries for them:		Sequences aren't included in that GRANT ALL PRIVILEGES. We can generate GRANT queries for them:

	SELECT 'GRANT USAGE ON SEQUENCE ' \|\| sequence_schema \|\| '.' \|\| sequence_name \|\| ' TO acme;'		SELECT 'GRANT USAGE ON SEQUENCE ' \|\| sequence_schema \|\| '.' \|\| sequence_name \|\| ' TO "acme";'
	FROM information_schema.sequences		FROM information_schema.sequences
	WHERE sequence_schema NOT LIKE 'pg_%'		WHERE sequence_schema NOT LIKE 'pg_%'

2024-06-02T11:35:36Z

@@ Line 7: / Line 7: @@
 === Open a console ===
 Use peer authentication from the postgres user: <code>sudo -u postgres psql</code>
 === Create a new database or user ===

2024-06-02T11:23:39Z

Merge howto sections

@@ Line 64: / Line 64: @@
      WHERE sequence_schema NOT LIKE 'pg_%'
      AND sequence_schema != 'information_schema';
 == Clusters ==
@@ Line 80: / Line 94: @@
 |-
 | fantoir || Datasources || PostgreSQL version of FANTOIR for geocoding
 |}

2024-01-16T18:48:14Z

Troubleshoot: Airflow containers were down after PostgreSQL update

@@ Line 80: / Line 80: @@
 |-
 | fantoir || Datasources || PostgreSQL version of FANTOIR for geocoding
 |}

2024-01-15T00:27:44Z

Troubleshoot

← Older revision		Revision as of 00:27, 15 January 2024
Line 83:		Line 83:

	== Troubleshoot ==		== Troubleshoot ==
			=== An application stops to work correctly with PostgreSQL ===
			Issues with queries are logged to /var/log/messages:

			$ tail -n100 -f /var/log/messages \| grep postgres

			For example, {{T\|1943}} issue was found with this line:

			Jan 14 23:13:58 db-A-001 postgres[35351]: [8-1] 2024-01-14 23:13:58.081 UTC [35351] ERROR: unsupported XML feature

			=== Unsupported XML feature ===
			For Orbeon, XML support is required. That's not by default the case on FreeBSD, the port needs to be built manually. That's the case when provisioning a new server through {{Ops file\|roles/dbserver-pgsql/server/build.sls}} but upgrade can break this port.

			If so, rebuild the package. For that, you can follow the instructions of [[Operations grimoire/FreeBSD]], section PostgreSQL.

	=== Some postgres_privileges.present are always changed by Salt ===		=== Some postgres_privileges.present are always changed by Salt ===

2023-12-17T21:59:14Z

← Older revision		Revision as of 21:59, 17 December 2023
Line 81:		Line 81:
	\| fantoir \|\| Datasources \|\| PostgreSQL version of FANTOIR for geocoding		\| fantoir \|\| Datasources \|\| PostgreSQL version of FANTOIR for geocoding
	\|}		\|}

			== Troubleshoot ==
			=== Some postgres_privileges.present are always changed by Salt ===

			Some ON ALL TABLE privilege queries are always run by the Salt PostgreSQL execution module. For example:

			ID: dbserver_pgsql_user_orbeon_privilege_2_ALL
			Function: postgres_privileges.present
			Name: orbeon
			Result: True
			Comment: The privilege(s): ALL have been granted to orbeon
			Started: 21:54:53.656120
			Duration: 284.394 ms
			Changes:
			----------
			orbeon:
			Present

			Those statements aren't idempotent. This is a known issue, and doesn't affect deployment.

2023-12-17T21:56:32Z

← Older revision		Revision as of 21:56, 17 December 2023
Line 52:		Line 52:
	- ALL		- ALL

	See {{D\|3252}} for an example.		See {{D\|3252}} or {{D\|3253}} for an example.

			As a known issue, this query will ALWAYS be executed by Salt.

			; Sequences privileges

	Sequences aren't included in that GRANT ALL PRIVILEGES. We can generate GRANT queries for them:		Sequences aren't included in that GRANT ALL PRIVILEGES. We can generate GRANT queries for them:

2023-12-17T20:29:55Z

Grant privileges to an user