PostgreSQL major
versions are represented by the first two digit groups of the version number,
e.g., 8.4. PostgreSQL minor versions are represented by the third
group of version digits, e.g., 8.4.2 is the second minor release of 8.4. Minor
releases never change the internal storage format and are always compatible
with earlier and later minor releases of the same major version number, e.g.,
8.4.2 is compatible with 8.4, 8.4.1 and 8.4.6. To update between compatible
versions, you simply replace the executables while the server is down and
restart the server. The data directory remains unchanged — minor upgrades are
that simple.
For major releases
of PostgreSQL, the internal data storage format is subject to change, thus
complicating upgrades. The traditional method for moving data to a new major
version is to dump and reload the database, though this can be slow. A faster
method is pg_upgrade.
Replication methods are also available.
New major
versions also typically introduce some user-visible incompatibilities, so
application programming changes might be required. All user-visible changes are
listed in the release notes (Appendix E);
pay particular attention to the section labeled "Migration". If you
are upgrading across several major versions, be sure to read the release notes
for each intervening version.
Here
Host server -- test_db02
Archive location -- /u01/psqlwarch
PGDATA Location -- /var/lib/pgsql/data/
1 - ( By root user ) Confirm current installed version of postgres
[root@test_db02 ~]# su - postgres
-bash-4.1$ /usr/bin/pg_ctl -D
/var/lib/pgsql/data -l logfile start
server starting
-bash-4.1$ psql
psql (8.4.20)
Type "help" for help.
postgres=# \q
-bash-4.1$ rpm -qa postgres*
postgresql-server-8.4.20-2.el6_6.x86_64
postgresql-contrib-8.4.20-2.el6_6.x86_64
postgresql-odbc-08.04.0200-1.el6.x86_64
postgresql-libs-8.4.20-2.el6_6.x86_64
postgresql-docs-8.4.20-2.el6_6.x86_64
postgresql-8.4.20-2.el6_6.x86_64
2
- ( By root
user ) Download
pgdg-centos94-9.4-1.noarch.rpm form (http://yum.postgresql.org/repopackages.php) & install Postgres 9.4 related RPM by yum
[root@test_db02 u01]# rpm -ivh
pgdg-centos94-9.4-1.noarch.rpm
warning:
pgdg-centos94-9.4-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8:
NOKEY
Preparing...
########################################### [100%]
1:pgdg-centos94
###########################################
[100%]
[root@test_db02 u01]# yum install
postgresql94-server.x86_64 postgresql94.x86_64 postgresql94-contrib.x86_64
postgresql94-libs.x86_64 postgresql94-odbc.x86_64 postgresql94-docs.x86_64 postgresql94-jdbc.x86_64
postgresql94-debuginfo.x86_64 postgresql94-devel.x86_64
postgresql94-jdbc-debuginfo.x86_64 postgresql94-odbc-debuginfo.x86_64
postgresql94-plperl.x86_64 postgresql94-plpython.x86_64
postgresql94-pltcl.x86_64 postgresql94-test.x86_64
3
- ( By root
user ) Initialize
postgres 9.4 and configure
[root@test_db02 9.4]# service postgresql-9.4 initdb
Initializing database: [ OK ]
[root@test_db02 9.4]# pwd
/var/lib/pgsql/9.4
[root@test_db02 9.4]# ls -lrt
total 12
drwx------. 2 postgres postgres 4096 Jun 11 17:40 backups
-rw-------. 1 postgres postgres 2039 Jun 16 16:30
pgstartup.log
drwx------. 19 postgres postgres 4096
Jun 16 16:30 data
[root@test_db02 9.4]# cat
pgstartup.log
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.
fixing permissions on existing
directory /var/lib/pgsql/9.4/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
creating template1 database in
/var/lib/pgsql/9.4/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions
... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in
objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language
... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok
Success. You can now start the
database server using:
/usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
or
/usr/pgsql-9.4/bin/pg_ctl -D /var/lib/pgsql/9.4/data -l logfile start
[root@test_db02 9.4]# chkconfig
postgresql-9.4 on
4
- Backup data from 8.4 postgres
[root@test_db02 9.4]# su - postgres
-bash-4.1$ psql
psql (8.4.20)
Type "help" for help.
postgres=# \q
-bash-4.1$ pwd
/var/lib/pgsql
-bash-4.1$ pg_dumpall > dump.sql
Note :- It
is recommended that you use the pg_dump and pg_dumpall programs
from the newer version
of PostgreSQL, to take advantage of enhancements that might have been made
in these programs. Current releases of the dump programs can read data from any
server version back to 7.0.
5
- Shutdown postgres 8.4 and start postgres 9.4
& restore Backup
-bash-4.1$ ps -ef | grep postgres
postgres 3074
1 0 15:20 pts/0 00:00:00 /usr/bin/postgres -D
/var/lib/pgsql/data
postgres 3080
3074 0 15:20 ? 00:00:00 postgres: logger process
postgres 3082
3074 0 15:20 ? 00:00:02 postgres: writer process
postgres 3083
3074 0 15:20 ? 00:00:01 postgres: wal writer process
postgres 3084
3074 0 15:20 ? 00:00:00 postgres: autovacuum launcher
process
postgres 3085
3074 0 15:20 ? 00:00:00 postgres: archiver process last was 00000002000000000000001B
postgres 3086
3074 0 15:20 ? 00:00:00 postgres: stats collector
process
-bash-4.1$ /usr/bin/pg_ctl -D
/var/lib/pgsql/data stop
waiting for server to shut down....
done
server stopped
-bash-4.1$ /usr/pgsql-9.4/bin/pg_ctl
-D /var/lib/pgsql/9.4/data -l logfile start
server starting
-bash-4.1$ ps -ef | grep postgres
postgres 4001
1 0 16:40 pts/0 00:00:00 /usr/pgsql-9.4/bin/postgres -D
/var/lib/pgsql/9.4/data
postgres 4002
4001 0 16:40 ? 00:00:00 postgres: logger process
postgres 4004
4001 0 16:40 ? 00:00:00 postgres: checkpointer process
postgres 4005
4001 0 16:40 ? 00:00:00 postgres: writer process
postgres 4006
4001 0 16:40 ? 00:00:00 postgres: wal writer process
postgres 4007
4001 0 16:40 ? 00:00:00 postgres: autovacuum launcher
process
postgres 4008
4001 0 16:40 ? 00:00:00 postgres: stats collector
process
-bash-4.1$ psql -l
List of
databases
Name | Owner
| Encoding | Collation |
Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres
| postgres | UTF8 |
en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
(3 rows)
-bash-4.1$ psql < dump.sql
-bash-4.1$ psql -l
List of databases
Name | Owner
| Encoding | Collation |
Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres
| postgres | UTF8 |
en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres=CTc/postgres
:
=c/postgres
testdb
| postgres | UTF8 |
en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
6
- Config Network Access
vi
/var/lib/pgsql/9.4/data/postgresql.conf
1. listen_addresses =
'*'
2. port = 5432
/var/lib/pgsql/9.4/data/pg_hba.conf
7
- Remove PG8.4 & create softlink with PG 9.4
[root@test_db02 9.4]# rpm -qa
postgres*
[root@test_db02 9.4]# yum remove
postgresql
[root@test_db02 9.4]# yum remove
postgresql-odbc-08.04.0200-1.el6.x86_64 postgresql-libs-8.4.20-2.el6_6.x86_64
[root@test_db02 9.4]# ln -s
/usr/pgsql-9.4/bin/psql /usr/bin/psql
[root@test_db02 bin]# ln -s
/usr/pgsql-9.4/bin/pg_standby /usr/bin/pg_standby
[root@test_db02 bin]# which
pg_standby
/usr/bin/pg_standby
[root@test_db02 9.4]# su - postgres
-bash-4.1$ psql
psql (9.4.4)
Type "help" for help.
postgres=# \q
*************************************************************************
No comments:
Post a Comment