Différences

Ci-dessous, les différences entre deux révisions de la page.

--- infrastructure:db:postgresql:upgrade [17/12/2024 13:51] – créée Stéphane Paillet
+++ infrastructure:db:postgresql:upgrade [17/12/2024 14:10] (Version actuelle) – Stéphane Paillet
@@ Ligne 1: / Ligne 1: @@
-====== Montée de version Porgresql ======
+====== Montée de version Postgresql ======
 {{tag>SGBD BDD Postgresql upgrade}}
+Lors d'une montée de version de Debian inplace de Bullseye à Bookworm, je suis passé de Postgresql 13 à Postgresql15. Il fallait que je puisse migrer d'une version à l'autre la configuration et les données de la base de données.
 ===== Sauvegarde =====
 <code>
-sudo -u postgres pg_dumpall | gzip > peertube-$(date +'%Y-%m-%d').sql.tar.gz
+sudo -u postgres pg_dumpall | gzip > peertube-$(date +'%Y-%m-%d').sql.gz
+</code>
+<code>
+pg_lsclusters
+Ver Cluster Port Status Owner    Data directory              Log file
+  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
+  main    5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
+</code>
+<code>
+pg_dropcluster 15 main --stop
+</code>
+<code>
+sudo pg_upgradecluster 13 main
+</code>
+<code>
+Stopping old cluster...
+Restarting old cluster with restricted connections...
+Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
+Creating new PostgreSQL cluster 15/main ...
+/usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8
+The files belonging to this database system will be owned by user "postgres".
+This user must also own the server process.
+The database cluster will be initialized with locale "en_US.UTF-8".
+The default text search configuration will be set to "english".
+Data page checksums are disabled.
+fixing permissions on existing directory /var/lib/postgresql/15/main ... ok
+creating subdirectories ... ok
+selecting dynamic shared memory implementation ... posix
+selecting default max_connections ... 100
+selecting default shared_buffers ... 128MB
+selecting default time zone ... Europe/Paris
+creating configuration files ... ok
+running bootstrap script ... ok
+performing post-bootstrap initialization ... ok
+syncing data to disk ... ok
+Copying old configuration files...
+Copying old start.conf...
+Copying old pg_ctl.conf...
+Starting new cluster...
+Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
+Roles, databases, schemas, ACLs...
+ set_config
+------------
+(1 row)
+ set_config
+------------
+(1 row)
+ set_config
+------------
+(1 row)
+ set_config
+------------
+(1 row)
+Fixing hardcoded library paths for stored procedures...
+Upgrading database postgres...
+Analyzing database postgres...
+Fixing hardcoded library paths for stored procedures...
+Upgrading database peertube_prod...
+Analyzing database peertube_prod...
+Fixing hardcoded library paths for stored procedures...
+Upgrading database template1...
+Analyzing database template1...
+Stopping target cluster...
+Stopping old cluster...
+Disabling automatic startup of old cluster...
+Starting upgraded cluster on port 5432...
+Success. Please check that the upgraded cluster works. If it does,
+you can remove the old cluster with
+    pg_dropcluster 13 main
+Ver Cluster Port Status Owner    Data directory              Log file
+  main    5433 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
+Ver Cluster Port Status Owner    Data directory              Log file
+  main    5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
+</code>
+<code>
+pg_lsclusters
+Ver Cluster Port Status Owner    Data directory              Log file
+  main    5433 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
+  main    5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
+</code>
+<code>
+sudo pg_dropcluster 13 main
+</code>
+<code>
+sudo apt purge postgresql-13 postgresql-client-13
 </code>