Talk:PostgreSQL
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)
- 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.
- 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)
- It's not necessary for running
- 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.
- Currently there is no point in changing permissions for
/var/lib/postgres/
indeed, since only the subfolderdata
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 forpostgres
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)
- Currently there is no point in changing permissions for
- 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)
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)
- 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)
- 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)
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 ~~~~!
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)
Note that there is postgis-old-upgradeAUR to handle postgis in particular.
Blueyed (talk) 17:04, 19 January 2021 (UTC)
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)
- 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)
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)
- 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)
- 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)
pg_upgrade on bad shutdown
So attempting to use pg_upgrade will fail if "the cluster was not shutdown properly". This could happen during power outages or other various occations.
Would it be okay to add a section detailing a indirect solution such as:
docker run -it --rm -v /var/lib/postgresql/olddata:/var/lib/postgresql tianon/postgres-upgrade:15-to-16 bash su postgres $PGBINOLD/pg_ctl start -w -D $PGDATAOLD $PGBINOLD/pg_ctl stop -w -D $PGDATAOLD exit
Torxed (talk) 19:29, 13 August 2024 (UTC)
- Well the step 2. says it clearly: "Check the unit status to be sure that PostgresSQL was stopped correctly. If it failed, pg_upgrade will fail too." There is also one note at the bottom which is marked for removal... — Lahwaacz (talk) 11:12, 18 August 2024 (UTC)
General Additions to Upgrade Section
There are many caveats using pg_upgrade that can frustrate users to no end with failed version upgrades. A locale may be the same, but not specified in the database initdb string, etc. A far more flexible and robust way of handling postgresql version updates is simply to use pg_dump to dump the databases prior to upgrade and then pg_restore afterwards. This should be noted in the Upgrade section as this alternative avoids may problems with pg_upgrade and trying to install both the old and new versions.David C. Rankin, J.D.,P.E. -- Rankin Law Firm, PLLC (talk) 04:20, 22 November 2024 (UTC)
- The official documentation does not say that the dumping way is "far more flexible and robust". On the contrary, it notes that
pg_upgrade
is faster, which is an obvious advantage. — Lahwaacz (talk) 20:31, 29 November 2024 (UTC)