infrastructure:db:postgresql:upgrade [Grohub wiki]

Cette page est en lecture seule. Vous pouvez afficher le texte source, mais ne pourrez pas le modifier. Contactez votre administrateur si vous pensez qu'il s'agit d'une erreur.
====== 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.gz
</code>

<code>
pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
15  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
13  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
15  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
13  main    5433 down   postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
15  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>