Difference between revisions of "Updating PostgreSQL"
Kevin Wilkie (Talk | contribs) |
|||
(8 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
+ | [[Category:Database]] | ||
It is possible to do a file system copy of postgres and use the db tools to perform an upgrade | It is possible to do a file system copy of postgres and use the db tools to perform an upgrade | ||
+ | |||
+ | Here we will upgrade from pg94 (/opt/postgresql/pg94/) to pg95 (/opt/postgresql/pg95/) using a new server (could be done on same server) | ||
Create a folder to contain the original data on the new target server | Create a folder to contain the original data on the new target server | ||
− | /opt/postgresql/orig/ | + | mkdir /opt/postgresql/orig/ |
On the source (orig) server, stop postgres and move the current (should be small) data folder | On the source (orig) server, stop postgres and move the current (should be small) data folder | ||
− | /etc/init.d/postgresql- | + | /etc/init.d/postgresql-94 stop |
Line 10: | Line 13: | ||
cd /opt/ | cd /opt/ | ||
rsync -av postgresql root@172.21.40.132:/opt/postgresql/orig/ | rsync -av postgresql root@172.21.40.132:/opt/postgresql/orig/ | ||
+ | |||
+ | Restart postgres if still required | ||
+ | /etc/init.d/postgresql-94 start | ||
On the new server copy the existing data folder (just in case) | On the new server copy the existing data folder (just in case) | ||
Line 15: | Line 21: | ||
mv /opt/postgresql/pg95/data /opt/postgresql/pg95/orig95 | mv /opt/postgresql/pg95/data /opt/postgresql/pg95/orig95 | ||
mkdir /opt/postgresql/pg95/data | mkdir /opt/postgresql/pg95/data | ||
− | |||
chown -R postgres:postgres /opt/postgresql/pg95/data | chown -R postgres:postgres /opt/postgresql/pg95/data | ||
Line 21: | Line 26: | ||
su - postgres | su - postgres | ||
Create base db on server | Create base db on server | ||
− | /opt/postgresql/pg95/bin/initdb /opt/postgresql/pg95/data | + | /opt/postgresql/pg95/bin/initdb /opt/postgresql/pg95/data |
Exist "su" on new server and start service | Exist "su" on new server and start service | ||
exit | exit | ||
mkdir /opt/postgresql/pg95/data/pg_log | mkdir /opt/postgresql/pg95/data/pg_log | ||
chown postgres:postgres /opt/postgresql/pg95/data/pg_log | chown postgres:postgres /opt/postgresql/pg95/data/pg_log | ||
− | |||
On the new server run the following | On the new server run the following | ||
− | + | "su" to the postgres user | |
− | + | su - postgres | |
+ | cd /opt/postgresql/pg95/data | ||
+ | /opt/postgresql/pg95/bin/pg_upgrade -v -d /opt/postgresql/orig/postgresql/pg94/data -D /opt/postgresql/pg95/data -b /opt/postgresql/orig/postgresql/pg94/bin/ -B /opt/postgresql/pg95/bin/ | ||
+ | |||
+ | If you get a permissions problem "connection to database failed: fe_sendauth: no password supplied" add the following (in bold) to the /opt/postgresql/orig/postgresql/pg94/data/pg_hba.conf file | ||
+ | # TYPE DATABASE USER ADDRESS METHOD | ||
+ | # "local" is for Unix domain socket connections only | ||
+ | '''local all all''' | ||
+ | When complete exit from the "su" and start the postgres service | ||
+ | exit | ||
+ | /etc/init.d/postgresql-95 start |
Latest revision as of 11:54, 23 November 2018
It is possible to do a file system copy of postgres and use the db tools to perform an upgrade
Here we will upgrade from pg94 (/opt/postgresql/pg94/) to pg95 (/opt/postgresql/pg95/) using a new server (could be done on same server) Create a folder to contain the original data on the new target server
mkdir /opt/postgresql/orig/
On the source (orig) server, stop postgres and move the current (should be small) data folder
/etc/init.d/postgresql-94 stop
Now perform the copy
cd /opt/ rsync -av postgresql root@172.21.40.132:/opt/postgresql/orig/
Restart postgres if still required
/etc/init.d/postgresql-94 start
On the new server copy the existing data folder (just in case)
/etc/init.d/postgresql-95 stop mv /opt/postgresql/pg95/data /opt/postgresql/pg95/orig95 mkdir /opt/postgresql/pg95/data chown -R postgres:postgres /opt/postgresql/pg95/data
"su" to the postgres user
su - postgres
Create base db on server
/opt/postgresql/pg95/bin/initdb /opt/postgresql/pg95/data
Exist "su" on new server and start service
exit mkdir /opt/postgresql/pg95/data/pg_log chown postgres:postgres /opt/postgresql/pg95/data/pg_log
On the new server run the following "su" to the postgres user
su - postgres cd /opt/postgresql/pg95/data /opt/postgresql/pg95/bin/pg_upgrade -v -d /opt/postgresql/orig/postgresql/pg94/data -D /opt/postgresql/pg95/data -b /opt/postgresql/orig/postgresql/pg94/bin/ -B /opt/postgresql/pg95/bin/
If you get a permissions problem "connection to database failed: fe_sendauth: no password supplied" add the following (in bold) to the /opt/postgresql/orig/postgresql/pg94/data/pg_hba.conf file
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all
When complete exit from the "su" and start the postgres service
exit /etc/init.d/postgresql-95 start