dbawiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== PostgreSQL =====
  * [[https://www.enterprisedb.com/blog/postgresql-oracle-dba-database-administrator-differences-tools-comparison|PostgreSQL Through the Eyes of an Oracle DBA - Karen Jex]]
  * [[https://www.enterprisedb.com/postgres-tutorials/postgresql-replication-and-automatic-failover-tutorial|PostgreSQL Replication and Automatic Failover Tutorial - PostgreSQL Tutorials]]
  * [[https://www.youtube.com/watch?v=ntVjVNnilX0|The Evolution of Postgres High Availability]]
  * [[https://www.youtube.com/watch?v=ep7Br6gPzmU|PostgreSQL 12 Replication and Failover Tutorial]]
  * [[https://www.enterprisedb.com/promote/online-upgrade-using-logical-replicationOnline Upgrade Using Logical Replication]]
  * [[https://www.postgresqltutorial.com/postgresql-create-database/|Introduction to PostgreSQL CREATE DATABASE statement]]

==== Did you forget to allocate Huge Pages on your PostgreSQL server? ====
Reference: [[https://franckpachot.medium.com/did-you-forget-to-allocate-huge-pages-on-your-postgresql-server-7a97e7727b03|Did you forget to allocate Huge Pages on your PostgreSQL server?]]

When you install a Linux server, by default, there are no Huge Pages defined until you set vm.nr_hugepages in /etc/sysctl.conf and reboot or ‘sysctl -p’.

When you install PostgreSQL, by default, huge_pages=try which means that the postgres server will start with no error nor warning when huge pages are not available. This is mostly the equivalent of ‘LARGE_PAGES=TRUE’ in Oracle, except that Oracle will try to allocate as much as possible in Huge Pages.

This setting can be considered safer in case of unplanned reboot: prefer starting in degraded mode rather than not starting at all. But the risk is that you do not realize when the shared buffers are allocated in small pages.

Where?

First, how to know if the shared buffers were allocated in small or large pages? They are shared, and then show in pmap with ‘s’ mode. Here is my pmap output when allocated as Huge Pages:
<code>
$ pmap $(pgrep postgres) |  grep -E -- "-s- .*deleted" | sort -u
00007fa05d600000 548864K rw-s- anon_hugepage (deleted)
</code>
Here is the same when allocated as small pages:
<code>
$ pmap $(pgrep postgres) |  grep -E -- "-s- .*deleted" | sort -u
00007f129b289000 547856K rw-s- zero (deleted)
</code>
As far as I know, there’s no partially allocated shared buffer: if there are not enough huge pages for the total, then none are used.
...