pg_upgrade, Advanced Techniques
When it comes to performing postgres upgrades between major versions the command line utility pg_upgrade is the most popular method used today. But as with all things wonderful, there are caveats. One of the more critical issues is what to do when there's a failure. The purpose of this talk is to present those little covered "tricks" of the trade that allows one to improve and enhance the upgrade experience.
We'll start out by reviewing its basic mode operation. We'll then learn what makes it so darn fast when upgrading multi-terabyte datacluster in a matter of minutes. Finally, you will be introduced to those feared failures and how to deal with them with confidence and certainty.
Herein is a summary of the topics in this talk:
- How pg_upgrade works: the big picture
- About pgupgrade (comand line invocation)
- arguments and options
- The steps performing an upgrade
- About the replication ROLE
- with replication
- with login
- Upgrade options: COPY vs Hard Link
- Post-upgrade
- about performance
- aboutanalyze
- repack
- reindexing
- When something goes wrong at the point of no return (or is it?)
- Upgrading the REPLICA
- default method: pg_basebackup
- the cool method:
- - leveraging rsync
caveat; about vacuum wraparound
Видео
Другие доклады
-
22 мин
Ivan Panchenko Postgres ProfessionalPostgres Pro DBMS: what’s new & what’s on the roadmap
In this talk, you'll learn about Postgres Pro DBMS from the co-founder of Postgres Professional. Ivan will explain the philosophy behind this enhanced variant of PostgreSQL, reveal the differences between Postgres and Postgres Pro and provide the roadmap for the further evolution of Postgres Pro DBMS.
-
22 мин
Alexander Liubushkin ООО "ФОРС Телеком"
Yulia Golubeva ООО "ФОРС Телеком"New development of LUI (Live Universal Interface) - LUI4ORA2PG, migration tool
The report will talk about a new tool for migrating application systems from the Oracle environment to the Postgres environment. The tool is developed on the basis of the ora2pg tool (by Gill Darold) and the domestic LUI application development tool. Talks on LUI were given at past PGConfs in 2019 and 2020:
-
22 мин
Alexey Fadeev SibedgeMulticorn Foreign Data Wrapper vs plpython
Multicorn technology allows you to develop FDWs in Python, which is much easier and faster than creating FDWs in C. However, there is a downside, Multicorn FDWs work well with primitive WHERE conditions, but more complex cases cause difficulties, which I will talk about. Cases will be considered on the example of my Multicorn FDW for getting OpenStreetMap data. I will also show examples of using the same code in Multicorn FDW and plpython functions, including performance comparison. In conclusion, I will share my findings on when it is better to use plpython, and when Multicorn FDW is more preferable.
-
22 мин
Andrey Fefelov Mastery.proHow-to obfuscate Postgres database for load testing in web apps
Postgres is a well-known database for high load web applications. Such apps require stress/load testing itself to run properly in production. Besides obvious difficulties in preparation a test environment identical to production, generating proper traffic there is another one issue - database preparation for the test environment. And it seems it is not good to use the database from production in the testing environment in the era of personal data protection (GDPR, HIPAA). Data obfuscation is the rescue.
There are few instruments for data obfuscation in Postgres. During this session, I will tell you which of them we've selected and why what type of issues we faced, and if our solution was successful. You will know if it is possible to get an identical response on the test database without real data from production, we will observe some restriction on obfuscation, I'll present our utility which simplify things.