Too Many Postgresql Versions Installed: How to Start Only a Chosen Postgresql Version or Remove The Unneeded Ones? Needed Port Is The Standard "5432"

Too many postgresql versions installed: How to start only a chosen postgresql version or remove the unneeded ones? Needed port is the standard 5432

This situation with two clusters in Ubuntu may happen when upgrading to a newer release providing an newer postgresql version.

The automatic upgrade does not remove the old cluster, presumably for fear of erasing valuable data (which is wise because some postgres upgrades may require human work to be complete).

If you know you want to drop it, just run:

sudo pg_dropcluster --stop 9.1 main 

The corresponding data directory will be removed and service postgresql will no longer refer to 9.1

At this point the 9.2 cluster will still use the port 5433, which is unpractical.

To switch it to the default port, edit /etc/postgresql/9.2/main/postgresql.conf and change the line port = 5433 to port = 5432

Then restart PostgreSQL.

Finally to get rid of the postgresql-9.1 packages see the result of dpkg -l 'postgresql*9.1*'

Prevent Postgres 9.2 from starting

Ubuntu uses pg_wrapper to manage PostgreSQL installs. See the Ubuntu PostgreSQL wiki page.

You'll want to pg_dropcluster the 9.2 cluster, if you wish to actually destroy the old data. Or un-install PostgreSQL 9.2. Or modify the config file (don't remember the name right now) in /etc/postgresql/9.2/ that controls whether Pg starts or not. It's called something like start.conf or pg_ctl.conf or something.

You may also want to reverse the configured ports so your new 9.3 runs on 5432 and your not-started-by-default 9.2 tuns on 5433. That is in postgresql.conf.

postgresql port confusion 5433 or 5432?

/etc/services is only advisory, it's a listing of well-known ports. It doesn't mean that anything is actually running on that port or that the named service will run on that port.

In PostgreSQL's case it's typical to use port 5432 if it is available. If it isn't, most installers will choose the next free port, usually 5433.

You can see what is actually running using the netstat tool (available on OS X, Windows, and Linux, with command line syntax varying across all three).

This is further complicated on Mac OS X systems by the horrible mess of different PostgreSQL packages - Apple's ancient version of PostgreSQL built in to the OS, Postgres.app, Homebrew, Macports, the EnterpriseDB installer, etc etc.

What ends up happening is that the user installs Pg and starts a server from one packaging, but uses the psql and libpq client from a different packaging. Typically this occurs when they're running Postgres.app or homebrew Pg and connecting with the psql that shipped with the OS. Not only do these sometimes have different default ports, but the Pg that shipped with Mac OS X has a different default unix socket path, so even if the server is running on the same port it won't be listening to the same unix socket.

Most Mac users work around this by just using tcp/ip with psql -h localhost. You can also specify a port if required, eg psql -h localhost -p 5433. You might have multiple PostgreSQL instances running so make sure you're connecting to the right one by using select version() and SHOW data_directory;.

You can also specify a unix socket directory; check the unix_socket_directories setting of the PostgreSQL instance you wish to connect to and specify that with psql -h, e.g.psql -h /tmp.

A cleaner solution is to correct your system PATH so that the psql and libpq associated with the PostgreSQL you are actually running is what's found first on the PATH. The details of that depend on your Mac OS X version and which Pg packages you have installed. I don't use Mac and can't offer much more detail on that side without spending more time than is currently available.

How to upgrade PostgreSQL from version 9.6 to version 10.1 without losing data?

Assuming you've used home-brew to install and upgrade Postgres, you can perform the following steps.

1. Stop current Postgres server:

   launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

2. Initialize a new 10.1 database:

   initdb /usr/local/var/postgres10.1 -E utf8

3. run pg_upgrade (note: change bin version if you're upgrading from something other than below):

   pg_upgrade -v \
       -d /usr/local/var/postgres \
       -D /usr/local/var/postgres10.1 \
       -b /usr/local/Cellar/postgresql/9.6.5/bin/ \
       -B /usr/local/Cellar/postgresql/10.1/bin/
   

   -v to enable verbose internal logging

   -d the old database cluster configuration directory

   -D the new database cluster configuration directory

   -b the old PostgreSQL executable directory

   -B the new PostgreSQL executable directory

4. Move new data into place:

   cd /usr/local/var
   mv postgres postgres9.6
   mv postgres10.1 postgres
   

5. Restart Postgres:

   launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

6. Check /usr/local/var/postgres/server.log for details and to make sure the new server started properly.

7. Finally, re-install the rails pg gem

   gem uninstall pg
   gem install pg
   

I suggest you take some time to read the PostgreSQL documentation to understand exactly what you're doing in the above steps to minimize frustrations.

Postgresql adapter (pg): could not connect to server

Try adding host: localhost to your database.yml. (Based on: https://stackoverflow.com/a/10793186/919641)

pg Admin 4 - password for postgres user when trying to connect to PostgreSQL 13 server

I ran into the same problem recently. The solution below works for me. I'm using Windows btw, so you should try equivalent commands in your OS.

1. Change METHOD of all rows in your pg_hba.conf file from scram-sha-256 to trust
2. Add bin folder of Postgres installation to path, if you haven't
3. Open command prompt and enter psql -U postgres. You won't be asked for password here.
4. Enter \password postgres
5. Choose and confirm your password
6. Revert pg_hba.conf to original state

Now you should be able to enter password for postgres in pgAdmin.

Codelite using libpqxx connect example with g++ fails on macosx

Just learning and still got a long way to go, but discovered some linker options to make the compiler behave differently. I was able to remove all compiler warnings, linker warnings and now my code compiles.

using: -std=cxx17

in C++ Linker options which causes the compiler to use cxx2017 standards. Uhh, still need some reading on that one. I can understand it, but not deeply as yet.

Thomas

I think I have multiple postgresql servers installed, how do I identify and delete the 'extra' ones?

There can be some confusion over what people mean by an installation.

• The /etc/postgresql/ folder is the config folder for your clusters.
• The /var/lib/postgresql/ folder is for data.
• The program binaries for each version are in separate folders usually in /usr/lib/postgresql/.

I really don't know about /opt/postgresql as I don't have that on mine. But /opt is for "optional" binaries, so it's possible that your installation is here instead of/usr/lib/postgresql/.

In short, I think you may just have one installation which has files in multiple locations.

If you want to look at what you have installed, this may help:

How postgresql is structured:

To make things a little clearer postgres is structured as follows:

• A version literally refers to which version of the postgresql
  program binaries. Each installed version may have
  a cluster installed under that version. If not then that version is
  effectively dormant as it has no data or running server associated.

• Under each version there many be a number of clusters. You can think
  of the cluster as a running prostgres server (process). Each cluster
  has to have its own port/socket file for clients to connect to. Each
  cluster will be managed by a single version.

• Inside each cluster will be a number of databases. When a client
  connects it selects a DB to connect to. It can ask to change which
  DB it's connected to without opening a new session, but it can only
  ever be connected to one.

What have you got installed?

To find out which versions are installed you can look to dpkg and apt. You should be able to uninstall versions using apt and dpkg, but be very careful not to do this before you've checked what clusters are under each version.

To find out what clusters you have use the command pg_lsclusters. When I call this I get the following, you will get something different:

Version Cluster   Port Status Owner      Data directory                     Log file
9.1     main      5432 online <unknown>  /var/lib/postgres/data/9.1/main    /var/log/postgresql/postgresql-9.1-main.log

Pay careful attention to the "Status" column. If a cluster is not online then it's just data on disk and is doing nothing. If it is online then it is running.

How do you merge clusters?

You can copy the content from one cluster to another using the pg_dumpall command to generate a backup and use psql to import it to the cluster you want to keep. Its worth keeping backups of everything before you start.

How do you remove a cluster that is no-longer used?

• Use pg_lsclusters to get the details about the clusters and note the
  data directory and log file for those clusters.

• Use pg_ctlcluster <version> <cluster>
stop to stop the cluster.

• Remove the data folder and optionally the log file.

• Finally remove the data and config. The data folder should be /var/lib/postgres/data/<version>/<cluster> but check the output of pg_lsclusters to be sure of this. The config for the cluster: All clusters will have
  their own config folder in
  
  /etc/postgresql/<version>/<cluser>/.

Why did you get multiple clusters if you never asked for them?

Usually you have to specifically request a cluster to be created to get a new one. The only exception to this is when you upgrade a cluster, it will effectively create a new one and leave the old one in place.

org.postgresql.util.PSQLException: FATAL: sorry, too many clients already

We don't know what server.properties file is that, we neither know what SimocoPoolSize means (do you?)

Let's guess you are using some custom pool of database connections. Then, I guess the problem is that your pool is configured to open 100 or 120 connections, but you Postgresql server is configured to accept MaxConnections=90 . These seem conflictive settings. Try increasing MaxConnections=120.

But you should first understand your db layer infrastructure, know what pool are you using, if you really need so many open connections in the pool. And, specially, if you are gracefully returning the opened connections to the pool

---

Related Topics