As it does; Debian will bastardise the installation of postgresql in the usual manner, but it does offer a reasonable way to remove the bastardisation and configure it yourself so that you can have the data directory exactly where you want it.
The idea behind having a separate logical volume for the data is that if the worst comes to the worst and for some reason you have to reinstall the operating system, the data will still be there when the operation is complete. If you have partitioned the volumes according to the previous section then you will need to follow this guide in order to make use of the partitions that you created.
As always we start with the install
apt-get install postgresql postgresql-common postgresql-client
The installation process automatically creates the database, creates a user and group called “postgres” and starts the postgresql service. As we want to reconfigure it, the first thing we need to do is stop the service using our favorite method (be it systemctl or service):
service postgresql stop
If you like you can check that the service is indeed stopped with “service postgresql status”. The next thing that we need to do is get rid of the database and cluster that the installation process created. Please note that this step is mandatory as it will not be possible to create the new cluster until the old one has been deleted.
At the time of updating the latest version of postgresql on Debian is 15. If you are running a different version then you will need to change where it says “9.4” in this documentation with whatever version you are installing. to get rid of the old data run
pg_dropcluster 15 main
Once the old cluster has gone then you can create a new one. To do this simply run
pg_createcluster -d /pgsql/15 15 main
This will create the new cluster in the correct directory. Note that postgresql does not like its database being directly installed on a mount point which is one reason why we use the ./15 directory. The second being that it makes upgrading easier.
Note that pg_createcluster will set the correct privileges and the correct place for the log file (/var/log/postgresql) automatically if you run it as root.
At this point you should be able to start the service
service postgresql start
If it takes a long time to start then it probably hasn’t. The big problem here is that when you run “service postgresql status” it will likely say that it is active, but when you try to connect with psql you will get a message that says
sql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Note that you will not actually be able to connect to the database at this stage because of permissions in pg_hba.conf, but you should at least get the message
psql: FATAL: Peer authentication failed for user "postgres"
rather than the previous message.
If for some reason it goes tits up then try reconfiguring both postgresql and postgresql-common.
dpkg-reconfigure postgresql dpkg-reconfigure postgresql-common
In the case of postgresql-common you may be asked if wish to enable ssl by default in new clusters. Select “yes” for this option; albeit we now configure ssl manually so we can use it for replication.
One final action is to edit the pg_hba.conf file to trust the user postgres locally.
vi /etc/postgresql/15/main/pg_hba.conf
Peer authentication works be reading the logged on user so if your pg_hba.conf file has an entry like this
local all postgres peer
then you will need to change to the user “postgres” before you can run psql
$su postgres $psql
You can change it to “trust” if you wish however there re security implications for this so it is not recommended
local all postgres trust
Once you have done this, restart the postgresql service again and you should be able to connect to the server using
psql –U postgres
That’s about it for the postgresql install however you will have to configure the authentication before you can make much use of it.
Addendum:
SERVICE FAILS ON REBOOT (NAS Storage)
If you have set up your cluster on a nas of some description or other and are mounting it in fstab with nfs, sshfs, etc. then there is a possibility that the postgresql service will fail on start up but start if your run it manually. This is because it is trying to start before the mount is available. To resolve this you need to edit the service file (which at the time of this update I was using postgresql 11 so the service file was:
/run/systemd/generator/postgresql.service.wants/postgresql@11-main.service
if you look in the file you will note a declaration RequiresMountsFor followed by a space separated list. If you append this file with the mountpoint where you have created the cluster and reboot then everything should work.
RequiresMountsFor=/etc/postgresql/%I /var/lib/postgresql/%I /pgsql
Note: The “%I” is explained within the file, it is not necessary to append this to the nas mount. Just remember that if you update postgresql you will probably have update this manually.
Once rebooted type systemctl and review the services to make sure they all started, and to make doubly sure log in to a database using /psql.
AUTHENTICATION
As of version 15 postgresql now defaults to sha-256 but it is still worth checking that this is the case. Open the postgresql.conf file in your favourite editor
vim /etc/postgresql/13/main/postgresql.conf
and find the password_encryption directive
#password_encryption = md5 # md5 or scram-sha-256
If the line is commented out then uncomment it and change the value to scram-sha-256
Once you have done that change “md5” to “scram-sha-256” in /etc/postgresql/15/main/pg_hba.conf and restart postgresql
If you already have passwords set then you will need to re-enter or change them. The easiest way to do this when you are changing a few is to use psql
$su postgres $psql
and then issue the \password command and follow the instructions on screen
\password postfixro
to change the “postfixro” user’s password for example.
UPGRADING POSTGRESQL (Debian servers)
If you have followed these instructions and have your database in a non default location then upgrading it is slightly different to most instructions on the internet. These instructions are for upgrading from 11 to 13 but should work equally well for other versions.
First thing to do is backup the database. First thing to do is create a directory to store the backup (this must be empty)
mkdir ~/pgbackup
Now run pg_basebackup to backup the existing database
pg_basebackup -h localhost -U replicate -P -D ~/pgbackup -F t
The previous command assumes you have set up a user called “replicate“. If not then use a username that has sufficient permissions to perform the backup.
You will then be prompted for a password.
Now run “pg_lsclusters“. This will list all clusters and their locations.
Ver Cluster Port Status Owner Data directory Log file 11 main 5432 online postgres /pgsql/data /var/log/postgresql/postgresql-11-main.log 13 main 5433 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
You will note that we have 2 clusters (a version 11 one and a version 13 one). If you check the data directory you will see that the one that we are using is “/pgsql/data“. The version 13 one is in the standard Debian location. We need to get rid of that one before we go any furhter.
Next thing to do is stop postgresql and then we can drop the version 13 database with the command:
pg_dropcluster 15 main
Once we have rid of the default version 15 cluster we can create one of our own but first we need to find somewhere to put it so we need to make a new clean directory in our /pgsql folder.
mkdir /pgsql/15
Now we can upgrade our version 13 cluster to version 15.
sudo -u postgres pg_upgradecluster -v 15 13 main /pgsql/15
This can take some time if the databases are large but should complete eventually.
To check that the upgraded cluster exists you can use pg_lsclusters and also have a look in /pgsql/15 to see if the cluster directory structure is there. This time pg_lsclusters should show something like
Ver Cluster Port Status Owner Data directory Log file 11 main 5433 down postgres /pgsql/13 /var/log/postgresql/postgresql-%Y-%m-%d_%H%M%S.log 13 main 5432 down postgres /pgsql/15 /var/log/postgresql/postgresql-%Y-%m-%d_%H%M%S.log
One of the important things to check here is the port. The version 15 needs to be running on port 5432 or you will have to reconfigure all the services that use your databases. The port is set in /etc/postgresql/15/main/postgresql.conf
You will note at this point that both clusters are marked as down. If you start the postgresql service now then it will appear as though it is running however if you try and connect with psql then it will fail. We can however start the cluster manually and connect to test by issuing the following command:
pg_ctlcluster 15 main start
Now if you try psql then it should connect, as should your application.
To check that the cluster is live run pg_lsclusters again and it should indicate version 13 is online
11 main 5433 down postgres /pgsql/data /var/log/postgresql/postgresql-%Y-%m-%d_%H%M%S.log 13 main 5432 online postgres /pgsql/13 /var/log/postgresql/postgresql-%Y-%m-%d_%H%M%S.log
If you reboot or restart postgresql now then it will fail again as we need to tell systemd that we want to start the cluster automatically. To do so simply run
systemctl enable postgresql@15
We also need to edit /etc/postgresql/15/main/start.conf to change “manual” to “auto”
# This cluster was upgraded to a newer major version. The old # cluster has been preserved for backup purposes, but is not started # automatically. auto
Now if we restart postgresql in the ordinary manner then it should work fine.
If all is well you can disable and drop the old cluster (albeit I normally like to keep it around for a few weeks just to make sure nothing has gone too wrong during the upgrade. When you want to get rid of it just run
systemctl disable postgresql@13 pg_dropcluster 13 main
This should complete the upgrade steps.
As a final check if you go to the postgresql command prompt and issue the following command it should tell you that you are running version 15:
SELECT VERSION();
psql -U postgres
psql (15.7 (Debian 15.7-0+deb12u1))
Type "help" for help.
postgres=# SELECT VERSION();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.7 (Debian 15.7-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
postgres=#
This upgrade process should work find for other versions, you simply need to change the version numbers.
Recent Comments