Postgres

PostegreSQL notes

   sudo -u postgres psql -c "SELECT version();"

Default clusters and upgrading

When installing a postgresql-NN package from scratch, a default cluster 'main' will automatically be created. This operation is equivalent to doing 'pg_createcluster NN main --start'.

Due to this default cluster, an immediate attempt to upgrade an earlier 'main' cluster to a new version will fail and you need to remove the newer default cluster first. E. g., if you have postgresql-13 installed and want to upgrade to 15, you first install postgresql-15:

 apt-get install postgresql-15

Then drop the default 15 cluster that was just created:

 pg_dropcluster 15 main --stop

And then upgrade the 13 cluster to the latest installed version (e. g. 15):

 pg_upgradecluster 13 main

(source: /usr/share/doc/postgresql-common/README.Debian.gz)

 sudo systemctl start postgresql@15-main
 sudo systemctl enable postgresql@15-main
 sudo apt-get remove postgresql-13 postgresql-client-13

mac with brew

To start postgresql@15 now and restart at login:

 brew services start postgresql@15

Or, if you don't want/need a background service you can just run:

 LC_ALL="C" /usr/local/opt/postgresql@15/bin/postgres -D /usr/local/var/postgresql@15