Talk:PostgreSQL

From ArchWiki
Latest comment: 9 December 2023 by Lahwaacz in topic Change default data directory

Why is /var/lib/postgres/ owned by root?

Having a UNIX user which does not have permissions to write into its own home directory is somewhat perverse. It causes multiple problems, e.g. [1], psql run as postgres cannot write to ~/.psql_history and so on.

Even the official documentation says that it is recommended to have /usr/local/pgsql (their equivalent of /var/lib/postgres) to be owned by the postgres user.

Does anybody disagree or shall I create a bug report to change it in the Arch package?

-- Lahwaacz (talk) 16:02, 23 November 2017 (UTC)Reply[reply]

While your points are valid but then in normal case only data directory is accessed by postgres. So why give additional permission? psql can be run as normal user instead of postgres user if psql_history is required at all.
Also many other system users have / as home directory which is also owned by root. So its not compulsory that home directory must be write-able.
I do not disagree with you but I would also say its not necessary.
-- Amish (talk) 17:29, 23 November 2017 (UTC)Reply[reply]
It's not necessary for running postgresql.service itself, but the postgres user is used for maintenance as well. Other system users with / as home directory are never used for interactive shell so they also specify either /usr/bin/nologin or /bin/false as the default shell. -- Lahwaacz (talk) 18:07, 23 November 2017 (UTC)Reply[reply]
My point is that if things are working without permission then why give permission? Not giving permission does not break or disturb any maintenance. And psql can be run as psql -U postgres from own user account if one wants history.
-- Amish (talk) 00:53, 24 November 2017 (UTC)Reply[reply]
Currently there is no point in changing permissions for /var/lib/postgres/ indeed, since only the subfolder data is used. This is because the default upstream configuration is to have this {{ic|data} folder where postgresql is installed (i.e. `/usr/local/postgresql/` by default), in which case you want to have your DB as a subdir and no rights for postgres on the folder above, which contains the binary in particular. If we ever remove that uneeded level (and thus use /var/lib/postgres/ for DBs), this would obviously change. Archange (talk) 08:58, 5 June 2018 (UTC)Reply[reply]
Finally, I’ve seen a valid point in changing that, since the current situation prevented a simplification of the upgrade path for major version as it was hinted in https://wiki.archlinux.org/index.php?title=PostgreSQL&diff=next&oldid=589841. So maybe we should reconsider… Archange (talk) 22:43, 18 February 2020 (UTC)Reply[reply]

Upgrade from 10 to 11

I've just (belatedly) updated Postgresql from version 10 to 11, and had a couple of problems.

pg_upgrade failed, and I couldn't see anything that looked like a helpful error message, so I resorted to the manual method as described in section 7.1 of this page.

That initially failed too, but I got it to work by using /opt/pgsql-10/bin/pg_dumpall instead of just pg_dumpall. So the steps I used were:

 # systemctl stop postgresql.service
 # mv /var/lib/postgres/data /var/lib/postgres/olddata
 # mkdir /var/lib/postgres/data
 # chown postgres:postgres /var/lib/postgres/data
 [postgres]$ initdb -D '/var/lib/postgres/data'
 [postgres]$ /opt/pgsql-10/bin/pg_ctl -D /var/lib/postgres/olddata/ start
 [postgres]$ /opt/pgsql-10/bin/pg_dumpall -f /tmp/old_backup.sql
 [postgres]$ /opt/pgsql-10/bin/pg_ctl -D /var/lib/postgres/olddata/ stop
 # systemctl start postgresql.service
 [postgres]$ psql -f /tmp/old_backup.sql postgres

ChrisDennis (talk) 18:26, 18 November 2018 (UTC)Reply[reply]

It is hard to argue about errors without seeing them. Upstream recommends to use pg_dumpall from the newer version, so you should probably ask upstream about the problems. -- Lahwaacz (talk) 18:43, 18 November 2018 (UTC)Reply[reply]
I failed to note the exact errors at the time, but one related to a version of a library being missing, so I assumed that the problems were caused by the fact that my whole system had been very out of date before I did a system update and then upgraded postgresql. My point was that a tweak to the instructions on this page was required to solve the problem. ChrisDennis (talk) 18:45, 23 November 2018 (UTC)Reply[reply]

pg_upgrade problem if extensions (like postgis) are used

If a database has an extension installed that required extra libraries (like the case of postgis), one ends up with a serious problem with the pg_upgrade path.

The reason is that the postgresql-old-upgrade package does not have any extra libraries but the postgresql itself.

For instance if I try to migrate the 11 database (I am migrating to 12), I get:

 $ pg_upgrade -b /opt/pgsql-$PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/data11 -D /var/lib/postgres/data -c
 Performing Consistency Checks
 -----------------------------
 Checking cluster versions                                   ok
 Checking database user is the install user                  ok
 Checking database connection settings                       ok
 Checking for prepared transactions                          ok
 Checking for reg* data types in user tables                 ok
 Checking for contrib/isn with bigint-passing mismatch       ok
 Checking for tables WITH OIDS                               ok
 Checking for invalid "sql_identifier" user columns          ok
 Checking for presence of required libraries                 fatal
 Your installation references loadable libraries that are missing from the
 new installation.  You can add these libraries to the new installation,
 or remove the functions using them from the old installation.  A list of
 problem libraries is in the file:
     loadable_libraries.txt
 Failure, exiting
 $ cat loadable_libraries.txt 
 n'a pas pu charger la bibliothèque « $libdir/postgis-2.5 » : ERREUR:  n'a pas pu accéder au fichier « $libdir/postgis-2.5 » : Aucun fichier ou dossier de ce type
 Base de données : rechtem
 n'a pas pu charger la bibliothèque « $libdir/rtpostgis-2.5 » : ERREUR:  n'a pas pu accéder au fichier « $libdir/rtpostgis-2.5 » : Aucun fichier ou dossier de ce type
 Base de données : rechtem

This was because postgis was just upgraded to 3.0, but even restoring the 2.5 package failed:

 $ cat loadable_libraries.txt 
 could not load library "$libdir/postgis-2.5": ERREUR:  n'a pas pu charger la bibliothèque « /usr/lib/postgresql/postgis-2.5.so » : /usr/lib/postgresql/postgis-2.5.so: undefined symbol:   AllocSetContextCreateExtended
 Database: rechtem
 could not load library "$libdir/rtpostgis-2.5": ERREUR:  bibliothèque « /usr/lib/postgresql/rtpostgis-2.5.so » incompatible : versions différentes
 DETAIL:  La version du serveur est 12, celle de la bibliothèque est 11.
 Database: rechtem

I think a warning should be placed about that problem. Backup to 11 may not be obvious in case the problem is discovered too late.

—This unsigned comment is by Mrechte (talk) 14:07, 29 November 2019 (UTC). Please sign your posts with ~~~~!Reply[reply]

Also refer to the PostGIS upgrade manual. I was upgrading from PostGIS 2.5.something to 3.0.1, which has no mention of a "hard upgrade" in the release notes. I'm no expert so I won't be editing the page, but I did find postgis-old-upgradeAUR in AUR. After installing that, the pg_upgrade process failed with a new error:

 Your installation references loadable libraries that are missing from the
 new installation.  You can add these libraries to the new installation,
 or remove the functions using them from the old installation.  A list of
 problem libraries is in the file:
     loadable_libraries.txt

That file refers to postgis-2.5, rtpostgis-2.5, postgis_topology-2.5. I tried to fix this using the old version but it failed:

 $ sudo -u postgres /opt/pgsql-11/bin/postgres --single mydatabase -D /var/lib/postgres/data
 backend> ALTER EXTENSION postgis UPDATE TO "3.0.1";
 2020-03-02 09:20:22.909 CET [20289] ERROR:  extension "postgis" has no update path from version "2.5.1" to version "3.0.1"

This is mentioned on http://postgis.net/docs/manual-3.0/postgis_installation.html#soft_upgrade_extensions and the solution is to "backup your database, create a fresh one [...] and then restore your backup ontop of this new database."

So I ended up following the "Manual dump and reload" guide, which seems to have done the trick.

Thomastc (talk) 09:30, 2 March 2020 (UTC)Reply[reply]

Note that there is postgis-old-upgradeAUR to handle postgis in particular.

Blueyed (talk) 17:04, 19 January 2021 (UTC)Reply[reply]

Change the packaging naming

IMHO, the upgrade way chosen by Arch is not good. If you take a RedHat family distribution, PostgreSQL is packaged with its major version embedded (postgresql10, postgresql11, ...). This allow to install two versions of PG on the same system, easing the upgrade (as all necessary third party extensions can be installed on both versions). The only (slight) problem is that one has to maintain a link to the current PG bin/ directory in order that client commands like psql are found.

Therefore I would suggest we change the packaging naming in order to follow a more standard way of installing (and upgrading) PG.

Marc Rechté (talk) 09:06, 19 April 2020 (UTC)Reply[reply]

The wiki is not the place to suggest changes to Arch packages - use the bug tracker or talk directly to the package maintainer(s). The wiki merely documents the reality, it does not enforce it. -- Lahwaacz (talk) 10:08, 19 April 2020 (UTC)Reply[reply]

Optional vacuumdb upgrade step

Re: https://wiki.archlinux.org/index.php?title=PostgreSQL&oldid=727329 - IMO the vacuumdb --analyze-in-stages step should be present in the upgrade instructions. When the data import completes, Postgres is unable to properly optimize queries until statistics are regenerated, and that results in poor performance shortly after the upgrade. The linked page explains why these stats are needed. DragoonAethis (talk) 10:33, 24 April 2022 (UTC)Reply[reply]

I still don't see any word about this in the upstream documentation [2], [3]. It seems they removed the script because it was useless (calling a single command), but they don't recommend to do this during the upgrade. If anything, it should be moved to a separate section mentioning when it is useful to vacuum, considering all the options like autovacuum. — Lahwaacz (talk) 12:15, 24 April 2022 (UTC)Reply[reply]
Official docs don't say this, but pg_upgrade does right after the upgrade - see [4] - and now it just prints the vacuum command while previously it printed the script's name. Although good point on vacuuming, I'll try to write a small section on this in a bit. DragoonAethis (talk) 13:13, 24 April 2022 (UTC)Reply[reply]

Change default data directory

I suggest to use mount bind [1] instead of overwriting PGROOT in systemd otherwise auto upgrade will never run on major changes.

Relevant logs when during major upgrade on postgresql,


upgrading postgresql...

"/var/lib/postgres/data" is missing or empty. Use a command like

su - postgres -c "initdb --locale en_US.UTF-8 -D '/var/lib/postgres/data'"

with relevant options, to initialize the database cluster.


[1]: Fstab#Bind mount

Ms (talk) 11:32, 1 December 2023 (UTC)Reply[reply]

What is "auto upgrade"? It's not mentioned in the article, I don't see anything automatic in the postgresql package and I can't find any relevant information in my google. — andreymal (talk) 11:40, 1 December 2023 (UTC)Reply[reply]
Oh ... you were right, there is no auto upgrade in Arch. I must have confused with other things. My mistake, sorry. -- Ms (talk) 12:03, 1 December 2023 (UTC)Reply[reply]
There is also a better way to modify postgresql.service than editing /usr/lib/systemd/system/postgresql.service directly: Systemd#Editing provided units. Closing. — Lahwaacz (talk) 13:15, 9 December 2023 (UTC)Reply[reply]