Postgresql upgrade from 9.2 to 9.6 on Ambari 2.7 and HDP3.0

[#1]

Hi,

I'm trying to update the PostgreSQL from 9.2.24 to 9.6. I tried the following
Took backup of the DB with following command.

pg_dumpall > outputfile

Once the backup is done I stopped the service and Rename the pgsql directory under /var/lib/pgsql/

Updated the postgres with the following command :

yum install postgresql96-server postgresql96.

After the installation the new directory got created /var/lib/pgsql/9.6/. I ran the initdb command to create the cluster. Restored the old  pg_hba.conf and postgresql.conf files from the backup directory. Started the postgres service with the following command:

/usr/pgsql-9.6/bin/pg_ctl start -D /var/lib/pgsql/9.6/data

Once the service was up I restored the DB with the following command:
/usr/pgsql-9.6/bin/psql -d postgres -f outputfile

The DB restoration done but still while starting the ambari server  I'm getting the error  Ident authentication failed for user "ambari"

I have the doubt that is the upgrade properly done I mean the ambari server is using the 9.6 postgresql version that we installed. I have a doubt that still its using the old version when I give yum list postgresql it shows only the 9.2.4 version as installed. 

Can anyone help on this issue I fighting with this for 2 days.

[#2 · Accepted solution]

Yes I'm able to query the Database.Query Result

I tried with the following command
ambari-server setup --jdbc-db=postgres --jdbc-driver=/usr/share/java/postgresql-42.2.12.jar

Still ambari-server is not starting its giving the same error. Also tried rebooting the server also it didn't help.

[#3]

your problem is probably related to 

/var/lib/pgsql/<PGSQL_VERSION>/data/pg_hba.conf

please check you did not restore pg_hba config file to old postgresql version DIR in /var/lib/psql

if it's not the case

please try to allow any source connections (to eliminate this possible cause)

for ex by adding the following in the end of pg_hba 

vi /var/lib/pgsql/<NEW_POSTGRES_VERSION>/data/pg_hba.conf
host   all   all   0.0.0.0/0  md5

also check bellow file (for ex if you use many network interfaces in your host/VM):

vi /var/lib/pgsql/<NEW_POSTGRES_VERSION>/data/postgresql.conf
listen_addresses = '*'

and restart postgres 

[#4]

Thanks for the response.

I tried starting the service but still I'm getting the following error while starting postgresql.

/usr/pgsql-9.6/bin/pg_ctl start -D /var/lib/pgsql/9.6/data/
server starting
-bash-4.2$ LOG: skipping missing configuration file "/var/lib/pgsql/9.6/data/postgresql.auto.conf"
FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 9.6.17.

[#5]

after you renamed old installation pg DIR and created new one using initDb (in old installation DIR) a pg_upgrade step needs to be done (https://www.postgresql.org/docs/9.6/pgupgrade.html)

for ex ( with /usr/pgsql-9.6 as your new pgsql9.6 bin DIR installation path and /usr/bin/ as olg binDIR)

/usr/pgsql-9.6/bin/pg_upgrade --old-datadir /var/lib/pgsql/data/ --new-datadir /var/lib/pgsql/9.6/data/ --old-bindir /usr/bin/ --new-bindir /usr/pgsql-9.6/bin/

restart postgres after pg_upgrade is done

if (psql --version) gives old version update PATH

also systemd unit files etc... and pgsql_profile (/var/lib/pgsql/.pgsql_profile) / bash_profile  /etc/ files etc...

[#6]

I tried the pg_upgrade and it was successful. But still the ambari server is not starting it gives the same error.

Updated the systemb unit files with the new data directory. But still it didn't help to start ambari.

Is there any way to find out that ambari server is using the new updated postgres(9.6)?

If I run this command to start postgresql /usr/pgsql-9.6/bin/postgres -D /var/lib/pgsql/9.6/data/ It gives the following error message not sure is this normal.

< 2020-05-09 18:10:45.674 UTC > LOG: redirecting log output to logging collector process
< 2020-05-09 18:10:45.674 UTC > HINT: Future log output will appear in directory "pg_log".

I tired giving enter multiple times it stays on this and not coming out. The pg_log says like this

< 2020-05-09 18:10:45.677 UTC > LOG: MultiXact member wraparound protections are now enabled
< 2020-05-09 18:10:45.679 UTC > LOG: database system is ready to accept connections
< 2020-05-09 18:10:45.679 UTC > LOG: autovacuum launcher started

If the postgresql server is started but the ambari is not coming up. My goal is to update postgres to 9.6 and make ambari to use this version.

My assumption is that postgres upgrade is sucessfull but the ambari service start is not happening.

[#7]

could you confirm you can do queries on any database first and check ambari DB is restored

sudo -u postgres psql

\l+

...

if ok

could you check ambari jdbc connector is updatet ? (using adequate connector path, and if needed download it and install it)

ambari-server setup --jdbc-db=postgres --jdbc-driver=/usr/share/java/postgresql96-jdbc.jar

in case new jdbc version in installed in same location make sure old one is not already in use ( lsof kill or restart host/vm )

[#8 · Accepted solution]

Yes I'm able to query the Database.Query Result

I tried with the following command
ambari-server setup --jdbc-db=postgres --jdbc-driver=/usr/share/java/postgresql-42.2.12.jar

Still ambari-server is not starting its giving the same error. Also tried rebooting the server also it didn't help.

[#9 · Accepted solution]

Hi,

I'm able to solve the issue by running the ambari-server setup command again and select the 4 option instead of embedded DB. That solved the issue. Now I'm able to start the service without any issue.

Thanks,

GophalRaj

[#10]

Still the ambari server is not starting it failed in starting postgresql service this is the error log

ERROR 2020-05-09 08:40:02,291 ambari-server.py:997 - 'Fatal exception: Unable to start PostgreSQL server. Status stopped. . Exiting, exit code 3'
Traceback (most recent call last):
File "/usr/sbin/ambari-server.py", line 980, in main
action_obj.execute()
File "/usr/sbin/ambari-server.py", line 79, in execute
self.fn(*self.args, **self.kwargs)
File "/usr/lib/ambari-server/lib/ambari_commons/os_family_impl.py", line 89, in thunk
return fn(*args, **kwargs)
File "/usr/sbin/ambari-server.py", line 130, in start
server_process_main(args)
File "/usr/sbin/ambari_server_main.py", line 329, in server_process_main
ensure_dbms_is_running(options, properties, scmStatus)
File "/usr/lib/ambari-server/lib/ambari_server/dbConfiguration.py", line 603, in ensure_dbms_is_running
dbms.ensure_dbms_is_running(options, properties, scmStatus)
File "/usr/lib/ambari-server/lib/ambari_server/dbConfiguration_linux.py", line 447, in ensure_dbms_is_running
raise FatalException(retcode, err)
FatalException: 'Fatal exception: Unable to start PostgreSQL server. Status stopped. . Exiting, exit code 3'

One more thing after the postgres upgrade I tried editing the file /usr/lib/systemd/system/postgresql.service also tried reloading the daemon(systemctl daemon-reload). I adjusted the bin and data location as follows.

# Location of database directory
Environment=PGDATA=/var/lib/pgsql/9.6/data

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

ExecStartPre=/usr/pgsql-9.6/bin/postgresql-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-9.6/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/usr/pgsql-9.6/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/pgsql-9.6/bin/pg_ctl reload -D ${PGDATA} -s

Still the ambari failed to start with the same error.

Is this because the version is not compatiable or I'm doing anything wrong here.

Is there any proper way for upgrading the postgresql version on the ambari server via yum??