PostgreSQL 11 installation -- from source vs. through PG's repos

Question

I've been using PostgreSQL for 16 or 17 years (since around version 7.2 or 7.3). For whatever reason, I got started with using PG installing it from source. Due to inertia / fear-of-change, to this day I have never installed PG directly through the distro's repositories.

I'd like to change that --- not to the native distro's PG version, but rather to the postgres' provided repositories, so that I can install the latest version. The server I'm running is Debian 9/stretch.

I will certainly look through the docs, but I welcome any comments on the following aspects:

• I always follow the installation procedure shown in the "short version" --- but I don't see initdb or pg_ctl. Are there direct replacements for these?
• For that matter, I see a pg_wrapper that, according to the man page, "It determines the configured cluster and database for the user and calls the appropriate version of the desired program to connect to that cluster and database". That kind of gives me the creeps. Should it not? [give me the creeps?]

• I have a running 9.x installation that uses SQL_ASCII encoding and C locale. (originally installed from source following the "short version" installation procedure). How do I migrate to a fresh PG 11 install on a fresh/out-of-the-box server running Debian 9? This is what I usually do:

  • [postgres]$ pg_dumpall > dumpall.sql and copy the backup to the new machine.
  • Install from source on new machine, including /usr/local/pgsql/bin/initdb -D /home/postgres/data -E SQL_ASCII --locale=C (for the legacy application we're talking about, I cannot change this).
  • As user postgres, run:
    [postgres]$ psql -f dumpall.sql
     

  Any differences or any surprises awaiting when migrating to the repositories-based install?

• Does it play nice with Debian's unattended-upgrades?

• What about contrib modules? Our application uses pgcrypto; what I usually do is, before running psql to restore, I go to the downloaded source dir, cd contrib/pgcrypto → make → sudo make install (possibly also a postgres' restart?). Then, the dumpall.sql contains everything to import the pgcrypto functionality and create the necessary functions.

Any other aspects to watch out for?

Answer 1

I've using Debian PostgreSQL package for a couple of year and I have no problems, I can recommend this variant of installation.

And answers of your questions:

• When you install of Debian package, it will create a database named template. There is a "superuser" user - system user postgres, as usual. initdb and pg_ctl I have under /usr/lib/postgres/ver.sion/bin.

• Maybe I cannot understand your question, but I've tried to answer you: Debian can manage two or more versions of databases (but running on differents ports). I can run, for example, PostgreSQL version 10.0 on port 5432 (the default PostgreSQL port) and version 11.0 on port 5433. If I've have to upgrade to new version of PostgreSQL, Debian will install a new version to another port (5433) and there is a command pg_upgradecluster, which can make for you automagic upgrading of PostgreSQL from old to new version. After running this command, you will have on port 5432 (default PostgreSQL port) the new version of Po stgreSQL (in this case 11.0) and on the port 5433 will be the old (in this case versin 10.0) version of PostgreSQL. You can test it, and if everythink is OK, you can drop old custer and uninstall old version of PostgreSQL. I have a good experience with this process and I can recommend it. My process of upgrade is this:

  • pg_lscluster will list to you, which clusters are in use.

  • pg_dropcluster --stop 11.0 main (Debian will create a new cluster for a new version of PostgreSQL, but if I use a pg_upgradecluster, it will create a new cluster for me...)

  • pg_upgradecluster 10.0 main 11.0 (it will upgrade my old cluster to a new version of PostgreSQL and it will be change ports of old and new version: before invoke of this command, old version will be on port 5432 and new version on port 5433, after invoking of this command a new version will be on port 5432 and on port 5433 it will be old version of PostgreSQL).

• your migration procedure (pg_dumpall and then psql -f dump_file) is recomended by PostgreSQL (PG version migration) and I can recommend it. It will be OK.

• I had no problems with unattended-upgrades at all.

• I've not using pgcrypto module, but IMHO you can use this procedure (it will be works in the version 11, I hope): Install of pgcrypto module in PostgreSQL 8.4 under Ubuntu