How to migrate PostgreSQL databases from RHEL6 to RHEL7

最新推荐文章于 2024-08-27 07:35:46 发布

转载最新推荐文章于 2024-08-27 07:35:46 发布 · 308 阅读

文章标签：

#migrate #PostgreSQL #RHEL #6 #RHEL7

RHEL7 同时被 3 个专栏收录

432 篇文章

订阅专栏

PostgreSQL

211 篇文章

订阅专栏

RHEL6

117 篇文章

订阅专栏

本文介绍从旧版PostgreSQL迁移至Red Hat Enterprise Linux 7 (RHEL7) 的两种方法：就地升级与数据导出再导入。就地升级更快捷，而导出与导入方式更适用于跨架构升级。

https://access.redhat.com/articles/541873

Updated 2014年三月4日20:15 -

English

Migrate PostgreSQL databases to RHEL7

Red Hat Enterprise Linux 7 currently provides much newer major release of PostgreSQL than the version distributed in Red Hat Enterprise Linux 6. Those major versions are unfortunately not binary compatible so there is not possible to just upgrade to PostgreSQL server and run it against database files generated by older server.

Currently, there are two ways how to migrate your database content to be usable with upgraded PostgreSQL server. Either you may go the "in-place" upgrade way or you may use the older work-flow and dump & restore the database via SQL file. The former way is usually faster and encouraged (basically all you need to do here is to install newer PostgreSQL packages and then run one command) though this "howto" article describes also the later.

As there is possible to run multiple PostgreSQL servers on one machine, reader must take this into account and possibly perform most of the steps from this document multiple times (for each PostgreSQL service and its respective data files).

Backup your data first

Before all your migration work, please make sure you have a proper backup of all your system data (not only PostgreSQL related files). For this HOWTO article is especially important to have backed up the server data files (by default are placed in /var/lib/pgsql/data directory). Note that the PostgreSQL server must be off to make the backup of database (at FS level) consistent (so please run service postgresql stop before backing up).

If you plan to use the suggested in-place procedure, you are still encouraged to backup whole database dump. That will help if something goes wrong — then you will be still able to restore the database from SQL file without going back to RHEL6 and PostgreSQL 8.4 (which is not an easy task if you already upgraded whole system). So, for dumping the database please follow also the 1. and 2. step from the dump & restore approach.

Recommended: The in-place upgrade

This method does not work for cross architecture upgrades (even not among the same architecture family like from i686 to x86_64). So in this case, please use dump & restore approach.

Next steps describe the in-place upgrade process. In one sentence: You will update system (including postgresql-server package) and then the database files. So pretty straight forward. This process requires having the older PostgreSQL server on updated system (and have there also new pg_upgrade tool). But don't worry, you don't need to compile these yourself. For this purpose, the package postgresql-upgrade is distributed in Red Hat Enterprise Linux 7. Curious readers are encouraged to look at pg_upgrade(1) manual page (but it should not be necessary for successful upgrade).

All steps here are expected to be run as a root user.

You should firstly check following bullets:
- Basic configuration:
  Whether your server uses the default /usr/lib/pgsql/data directory. And whether the DB is correctly initialized, enabled, .. (but if not, you would not come here probably).
- Multiple PostgreSQL servers:
  I mentioned it at beginning, the more servers your system runs the more data directories must be handled independently. So be aware of all PostgreSQL servers running on your system.
- Plugins:
  If you are using some C functions compiled into PostgreSQL plugins on your RHEL6 system, you'll need have it also installed on RHEL7 machine later. So at this point, you should really know what plugins you have in use on RHEL6. By default, plugins are installed in /var/lib/pgsql, /var/lib64/pgsql respectively.
On the older RHEL6 system, ensure the old server is stopped (to make sure that data are not in inconsistent state).

Raw
```
el6~#> service postgresql stop
Stopping postgresql service:                                 [  OK  ]
```
You can ensure that the server is stopped:

Raw
```
el6~#> service postgresql status
postmaster is stopped
```
You must now either update your system to Red Hat Enterprise Linux 7 or you should have another machine running Red Hat Enterprise Linux 7. In both cases, make sure you have installed postgresql-server and postgresql-upgrade packages on RHEL7: # {#inplace3}

Raw
```
el7~#> yum install postgresql-server postgresql-upgrade
```
You will probably observe many changes between RHEL6 and RHEL7. The most important changes from PostgreSQL point of view are the PostgreSQL version change and the move to systemd. So on RHEL7 you should now use systemctl instead of the service & chkconfigtools to manage services.

Optionally (if you were using PostgreSQL plugins on RHEL6), you should (build and/or) install them also now on RHEL7. Building of plugins will require installing postgresql-devel package to allow compilation of plugin against the correct (currently installed) version of server.
Note that in next steps we will expect that your PostgreSQL data directory path on RHEL7 is set to its default place (/var/lib/pgsql/data); so you should now move data directory from RHEL6 machine to this default place on RHEL7 machine.
If you upgraded from RHEL6 to RHEL7 in place, you'll most probably skip this step as the /var/lib/pgsql/data is already there (if you haven't changed the default before).
Now comes the promised single command which should magically transform the database files. So run helper script postgresql-setup with upgrade parameter which does the job.

Raw
```
el7~#> postgresql-setup upgrade
Upgrading database: OK

The configuration files were replaced by default configuration.
The previous configuration and data are stored in folder
/var/lib/pgsql/data-old.

See /var/lib/pgsql/pgupgrade.log for details.
el7~#> echo $?
0
```
After this command finishes, you are encouraged to look at the contents of the /var/lib/pgsql/pgupgrade.log file for details (in case of fail - possible problems are also logged there). You should be able to iterate on fixing possible problems until the postgresql-setup upgradecommand succeeds. See the Known problems with PostgreSQL upgrade article.
Copy older configuration to new stack. The in-place upgrade does not copy (reuse) older configuration to newer data stack. The configuration is generated from scratch (that is to allow fluent upgrade). It may be desired to restore the old user tweaked configuration. As you can see from the command output above, the old configuration is stored in /var/lib/pgsql/data-old/*.conf so consider to move it to /var/lib/pgsql/data. Also consult the changes in PostgreSQL options between 8.4 and 9.2 version described at [2], [3] and [4].
If everything worked well, you may start the new server by:

Raw
```
el7~#> systemctl start postgresql
```
And check if everything works ok by:

Raw
```
el7~#> systemctl status postgresql
```
Now, because the server is running, you should do the last step which is suggested by pgupgrade.log — you should run analyze_new_cluster.sh script which is placed in postgres's home directory:

Raw
```
el7~#> su postgres -c '~/analyze_new_cluster.sh'
[.. snip ..]
Done
```
If you want to setup the PostgreSQL 9.2 server to be automatically started on boot of RHEL7 system, use systemctl:

Raw
```
el7~#> systemctl enable postgresql
```
Now you have successfully migrated PostgreSQL databases on your RHEL7 machine.

The dump & restore way

Next steps describe migration using dump data into SQL format.

Note that this way could be significantly slower than the in-place upgrade and the process may require some user's hand tweaking. You are encouraged to look at the official documentation [1] for deeper information.

All commands are expected to be run as root user.

Start old server (if not already running) on RHEL6.

Raw
```
el6~#> service postgresql start
```

Dump all databases contents into sql file on RHEL6.

Raw

el6~#> su - postgres -c "pg_dumpall > ~/pgdump_file.sql"

So now you can find the file here:

Raw

el6~#> su - postgres -c 'echo $HOME/pgdump_file.sql'
/var/lib/pgsql/pgdump_file.sql

Perform the step #3 from in-place upgrade howto (manage to run the RHEL7 machine, including the relevant plugins).
You should be prepared to copy the pgdump_file.sql onto the RHEL7 machine, so either it is already there (system upgraded from RHEL6) or you must copy it. In both cases, the file should end up on path:

Raw
```
el7~#> su - postgres -c 'echo $HOME/pgdump_file.sql'
/var/lib/pgsql/pgdump_file.sql
```
Also, you should have these files copied on proper place:

Raw
```
el7~#> su - postgres -c 'ls -1 $PGDATA/*.conf'
/var/lib/pgsql/data/pg_hba.conf
/var/lib/pgsql/data/pg_ident.conf
/var/lib/pgsql/data/postgresql.conf
```
Consult the changes in PostgreSQL options between 8.4 and 9.2 version described at [2], [3] and [4]. Also, please look at the step #2 in the in-place upgrade how-to above — you'll need again all plugins installed and properly set the PostgreSQL options.
Initialize data directory for new server on RHEL7:

Raw
```
el7~#> postgresql-setup initdb
```
Start the new server:

Raw
```
el7~#> systemctl start postgresql
```
Import data from sql file:

Raw
```
el7~#> su - postgres -c 'psql -f ~/pgdump_file.sql postgres'
```
This may require some adjusting of the sql file before it succeeds. After that, you are done.