PostreSQL Upgrade ProcessNewPush

First you need to change permissions on the localhost to trust:
```
# cd /var/lib/pgsql/data

# vi pg_hba.conf
```
Change the autorization/authentication for the line that starts with “local” to trust:
```
local        all                                           trust
```
And prevent clients to access the database by changing in postgresql.conf:
```
tcpip_socket = true
```
to
```
tcpip_socket = false
```
and
```
ssl = true
```
to
```
ssl = false
```

restart postgres:

# service postgresql restart

If you are having trouble restarting postgres, try to force the restart:

# su - postgres

$ pg_ctl -m immediate -D /var/lib/pgsql/data restart

Create a backup of the database:

# sudo -u postgres pg_dumpall > ~postgres/backups/full-[version]-[date].out

Shut down PostgreSQL
```
# service postgresql stop
```

Upgrade PostgreSQL

# rpm -Uvh /usr/src/redhat/RPMS/i686/postgresql-*rpm

Move the old db structure out of the way:

# mv /var/lib/pgsql/data /var/lib/pgsql/backups/data-[oldversion]

Start postgres back up:
```
# service postgresql start
```
Modify the pg_hba.conf file back to what we need it to be for the migration (see previously)

Restore the database:

sudo -u postgres psql -e template1 -f ~postgres/backups/full-[version]-[date].out > create.log 2> error.log

Restore the /etc/init.d/postgresql file with the logging:
```
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl  -l /var/log/pgsql -D $PGDATA -p /usr/bin/postmaster -o '-p ${PGPORT}' start  > /dev/null 2>&1" < /dev/null
```
Make sure to check the error.log, and if you encounter “permission denied” problems, make sure the the user (at top of the backup file) has the correct permissions set.
Restore pg_hba.conf and the postgresql.conf files to the original values

Restore the server certs

cp -a /var/lib/pgsql/backups/data-[version]/server.* /var/lib/pgsql/data

chown postgres.root /var/lib/pgsql/data/server.*

chmod 600 /var/lib/pgsql/data/server.*

Restore the postgres password to the correct pass:

ALTER USER "postgres" WITH PASSWORD 'secret-password-here' CREATEDB CREATEUSER

Restart PostgreSQL

PostreSQL Upgrade Process

About Balazs

Categories