NickName:BAE Ask DateTime:2016-01-20T05:10:42

postgresql: errors install and run postgresql-9.5

I installed two postgresql(s), 9.5 should use port 5433, 9.3.4 should use port 5432:

me@camb:/usr/lib/postgresql/9.5/bin$ ls -al psql
-rwxr-xr-x 1 root root 564464 Jan  7 14:54 psql
me@camb:/opt/pkgs/postgresql-9.3.4/bin$ ls -al psql 
-rwxr-xr-x 1 root root 405960 Aug  5 18:52 psql
me@camb:/opt/pkgs/postgresql-9.3.4/bin$ cat /etc/group
postgres:x:200:
me@camb:/opt/pkgs/postgresql-9.3.4/bin$ groups postgres
postgres : postgres ssl-cert

I wrote a script to install postgresql-9.5 and migrate from 9.3.4 to 9.5

Problem 0: postgresql-9.5 can not start sucessfully and why status is online

Output during install:

 * Starting PostgreSQL 9.5 database server
 * The PostgreSQL server failed to start. Please check the log output:
2016-01-19 19:41:49 UTC [16523-1] LOG:  database system was shut down at 2016-01-19 19:41:48 UTC
2016-01-19 19:41:49 UTC [16523-2] LOG:  MultiXact member wraparound protections are now enabled
2016-01-19 19:41:49 UTC [16522-1] LOG:  database system is ready to accept connections
2016-01-19 19:41:49 UTC [16527-1] LOG:  autovacuum launcher started
2016-01-19 19:41:49 UTC [16529-1] [unknown]@[unknown] LOG:  incomplete startup packet
   ...fail!
invoke-rc.d: initscript postgresql, action "start" failed.
dpkg: error processing postgresql-9.5 (--configure):
 subprocess installed post-installation script returned error exit status 1
dpkg: dependency problems prevent configuration of postgresql:
 postgresql depends on postgresql-9.5; however:
  Package postgresql-9.5 is not configured yet.
dpkg: error processing postgresql (--configure):
 dependency problems - leaving unconfigured
Processing triggers for libc-bin ...
No apport report written because the error message indicates its a followup error from a previous failure.
ldconfig deferred processing now taking place
Errors were encountered while processing:
 postgresql-9.5
 postgresql
E: Sub-process /usr/bin/dpkg returned an error code (1)
Error while installing Postgresql-9.5:1

Then I did some tests:

me@camb:/usr/lib/postgresql/9.5/bin$ sudo service postgresql start
 * Starting PostgreSQL 9.5 database server                                                                     [ OK ] 
me@camb:/usr/lib/postgresql/9.5/bin$ sudo service postgresql status
9.5/main (port 5433): online

What do the above errors?

By the way, another error is:

ERROR:  role "postgres" already exists

Problem 1: Why I cannot use /usr/lib/postgresql/9.5/bin/psql to loginto the database, but I can do this by /opt/pkgs/postgresql-9.3.4/bin/psql

me@camb:/usr/lib/postgresql/9.5/bin$ sudo -u postgres /opt/pkgs/postgresql-9.3.4/bin/psql -p 5433 reporting_central postgres
psql (9.3.4, server 9.5.0)
WARNING: psql major version 9.3, server major version 9.5.
         Some psql features might not work.
Type "help" for help.

reporting_central=# \q
me@camb:/usr/lib/postgresql/9.5/bin$ sudo -u postgres /opt/pkgs/postgresql-9.3.4/bin/psql -p 5432 reporting_central postgres
psql (9.3.4)
Type "help" for help.

reporting_central=# \q
me@camb:/usr/lib/postgresql/9.5/bin$ sudo -u postgres /usr/lib/postgresql/9.5/bin/psql -p 5433 reporting_central postgres
/usr/lib/postgresql/9.5/bin/psql: symbol lookup error: /usr/lib/postgresql/9.5/bin/psql: undefined symbol: PQsslInUse
me@camb:/usr/lib/postgresql/9.5/bin$ sudo -u postgres /usr/lib/postgresql/9.5/bin/psql -p 5432 reporting_central postgres
/usr/lib/postgresql/9.5/bin/psql: symbol lookup error: /usr/lib/postgresql/9.5/bin/psql: undefined symbol: PQsslInUse

The error is "undefined symbol: PQsslInUse". what does this mean? Why I cannot use /usr/lib/postgresql/9.5/bin/psql to loginto the database, but I can do this by /opt/pkgs/postgresql-9.3.4/bin/psql?

Thanks

By the way, I run the following to do migration:

sudo -u postgres /opt/pkgs/postgresql-9.3.4/bin/pg_dumpall -p 5432 | sudo -u postgres /opt/pkgs/postgresql-9.3.4/bin/psql -p 5433

Thanks

Copyright Notice：Content Author:「BAE」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/34886981/postgresql-errors-install-and-run-postgresql-9-5

Answers

ady 2016-09-08T20:46:22

You have to use the same libpq library as the one used in psql 9.5.\nYou should do the following:\n\nexport LD_LIBRARY_PATH=/usr/lib/postgresql/9.5/lib:$LD_LIBRARY_PATH\nsudo /sbin/ldconfig /usr/lib/postgresql/9.5/lib\n\n\nYou mess up with different versions of postgresql. \nTo start/use 9.5, you must use the appropriate binaries: initdb, pg_ctl and psql (for example): \n\nmkdir /usr/lib/postgresql/9.5/data\n/usr/lib/postgresql/9.5/bin/initdb -D ../data/\n/usr/lib/postgresql/9.5/bin/pg_ctl -D ../data/ -l logfile start\n/usr/lib/postgresql/9.5/bin/psql -p 5432 -d template1\n\n\nMore info: https://www.postgresql.org/docs/devel/static/install-post.html\nand PQsslInUse issue: http://postgresql.nabble.com/psql-error-on-postgresql-9-0-psql-symbol-lookup-error-psql-undefined-symbol-PQconnectdbParams-td5104930.html",

elynnaie 2016-02-11T03:07:56

\n The error is \"undefined symbol: PQsslInUse\"\n\n\nI just ran into this problem myself. I discovered that the reason was because I compiled Postgres 9.5 from scratch into a particular directory and then decided that I wanted it elsewhere and moved the install to a new directory. Down the line when I went to restore my database with psql, I got the above error. I corrected the problem by deleting my installation directory, changing the PREFIX to the correct location, recompiling and reinstalling Postgres. psql worked fine after this.",

Charis Ma 2018-06-14T08:01:52

I had the same problem \n\npsql: symbol lookup error: psql: undefined symbol: PQsetErrorContextVisibility\n\n\nAnd the solution is :\n\nexport LD_LIBRARY_PATH=$PGXZ_HOME/lib:${LD_LIBRARY_PATH}\n",