Matching PostgreSQL Client and Server Versions
Hi,
When PostgreSQL server and cluster versions don't match, PostgreSQL doesn't let you to run some functions, therefore you can not use efficiently and can not be advanced on it. To solve this problem you need to upgrade version or follow bellow steps.
You can try all of steps for your development or test environments.
I installed PostgreSQL server and client with different versions to show what is gonna happen If I try to dump. For sure, I can not get pg_dump!
-bash-4.2$ pg_dump -s testdb > testdb_dump.sql
pg_dump: server version: 9.6.3; pg_dump version: 9.2.18
pg_dump: aborting because of server version mismatch
-bash-4.2$ pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped
-bash-4.2$ mv /var/lib/pgsql /tmp/pgsql_cluster_old
mv: cannot move ‘/var/lib/pgsql’ to ‘/tmp/pgsql_cluster_old’: Permission denied
To move pgsql file, need to be root at this moment.
-bash-4.2$ logout
[root@gunce-test tmp]# mv /var/lib/pgsql /tmp/pgsql_cluster_old
Check PostgreSQL packages out and remove exists old version.
[root@gunce-test pgsql]# yum list installed |grep postgres*
postgresql96.x86_64 9.6.3-1PGDG.rhel7 @pgdg96
postgresql96-contrib.x86_64 9.6.3-1PGDG.rhel7 @pgdg96
postgresql96-libs.x86_64 9.6.3-1PGDG.rhel7 @pgdg96
postgresql96-server.x86_64 9.6.3-1PGDG.rhel7 @pgdg96
I have only version 9.6.
-bash-4.2$ /usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /var/lib/pgsql/9.6/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data -l logfile start
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data -l logfile start
server starting
-bash-4.2$ createdb sailormoon
-bash-4.2$ psql -dsailormoon
psql (9.6.3)
Type "help" for help.
sailormoon=#
As you see PostgreSQL client version is 9.6.3 now.
Next step is restoring old database which we moved to /tmp file in first step.
Loves,
When PostgreSQL server and cluster versions don't match, PostgreSQL doesn't let you to run some functions, therefore you can not use efficiently and can not be advanced on it. To solve this problem you need to upgrade version or follow bellow steps.
You can try all of steps for your development or test environments.
I installed PostgreSQL server and client with different versions to show what is gonna happen If I try to dump. For sure, I can not get pg_dump!
-bash-4.2$ pg_dump -s testdb > testdb_dump.sql
pg_dump: server version: 9.6.3; pg_dump version: 9.2.18
pg_dump: aborting because of server version mismatch
waiting for server to shut down.... done
server stopped
-bash-4.2$ mv /var/lib/pgsql /tmp/pgsql_cluster_old
mv: cannot move ‘/var/lib/pgsql’ to ‘/tmp/pgsql_cluster_old’: Permission denied
To move pgsql file, need to be root at this moment.
-bash-4.2$ logout
[root@gunce-test tmp]# mv /var/lib/pgsql /tmp/pgsql_cluster_old
Check PostgreSQL packages out and remove exists old version.
[root@gunce-test pgsql]# yum list installed |grep postgres*
postgresql96.x86_64 9.6.3-1PGDG.rhel7 @pgdg96
postgresql96-contrib.x86_64 9.6.3-1PGDG.rhel7 @pgdg96
postgresql96-libs.x86_64 9.6.3-1PGDG.rhel7 @pgdg96
postgresql96-server.x86_64 9.6.3-1PGDG.rhel7 @pgdg96
I have only version 9.6.
-bash-4.2$ /usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /var/lib/pgsql/9.6/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data -l logfile start
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data -l logfile start
server starting
-bash-4.2$ createdb sailormoon
-bash-4.2$ psql -dsailormoon
psql (9.6.3)
Type "help" for help.
sailormoon=#
As you see PostgreSQL client version is 9.6.3 now.
Next step is restoring old database which we moved to /tmp file in first step.
Loves,
Comments
Post a Comment