Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the methods of PostgreSQL upgrade". In the daily operation, I believe that many people have doubts about the methods of PostgreSQL upgrade. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "which methods of PostgreSQL upgrade". Next, please follow the editor to study!

The release cycle of a large version of PostgreSQL is usually one year, while PostgreSQL 12 is about to be released. The new version not only fixes Bug and provides new features, but also improves performance. For databases that use the old version but want to upgrade to a new version, PG upgrade methods are Logical Dump/restore, Binary In-Place Upgrades, and Logical Replication.

Logical Dump/restore

Logical import and export, reverse the database into ordinary SQL statements. The tools used are pg_dump/pg_dumpall and pg_restore, where pg_dump is for database level and pg_dumpall for instance level.

advantage

1.100% safe. It does not have any effect on the original example, so it is convenient for testing.

two。 Can be exported from the "read only" library, no performance impact on the original library.

3. Flexible. Schema or object can be specified and owner or access rights can be remapped / deleted.

Inferior position

1. The performance is poor. Although dump data can be paralleled, it is necessary to pay attention to whether there are performance problems when the amount of data exceeds 100G, and it will consume host resources when restore.

two。 Consistency is difficult to guarantee. The source database needs to be offline in dump, otherwise the data will still change during dump, then the source database and the target database will be in an inconsistent state.

Binary In-Place Upgrades

Upgrade in place, the tool used is pg_upgrade. Before executing pg_upgrade, install the new version of the binaries and initialize the instance with the new version of the software.

advantage

1. High performance. Especially when using the pg_upgrade-link-jobs X mode.

Inferior position

1. There is a certain risk and additional testing work is needed.

two。 All extensions and other modules need to be installed on the new instance.

3. There is no semi-online mode and must be completely offline.

4. If you do not use the-link mode, all backup servers will be lost.

5. Compared to the first startup, you need to execute analyze.

6. Take up double space.

Logical Replication

Logical replication, by configuring logical replication to achieve data synchronization between the source database and the target database.

advantage

1. Flexible. The target library can be allowed to change the mode.

two。 It's safe. It has almost no effect on the source database.

3. There is no need to stop.

Inferior position

1. The configuration is complex.

two。 If it is a large library, it takes a long time to configure and synchronize.

3. You may need to modify the schema, or at least REPLICA IDENTITY.

Comparative summary

The following is a comparison of the three methods:

At this point, the study of "what are the ways to upgrade PostgreSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.