DBA
Database Administrator.
DEB
Debian Package.
External Configuration Files
Files that are stored outside of PGDATA. For example, when you create a new PostgreSQL cluster using pg_createcluster (e.g., sudo pg_createcluster 14 main) in Ubuntu, it sets up a new data directory, typically under /var/lib/postgresql/, but the configuration files, like postgresql.conf and pg_hba.conf, are usually stored under /etc/postgresql/<version>/main/ to separate them from the actual data.
GCP
Google Cloud Platform
GPG
GNU Privacy Guard
IAC
Infrastructure As Code
ICT
Information and Communication Technology.
libpq
The C application programmer’s interface to Postgres. libpq is a set of library functions that allow client programs to pass queries to the Postgres backend server and to receive the results of these queries.
PGDATA
PostgreSQL data directory.
PGDG
Postgres Global Development Group.
PITR
Point-in-time Recovery.
RHEL
Red Hat Enterprise Linux.
RPM
Red Hat Package Manager.
RPO
Recovery Point Objective. The maximum targeted period in which data might be lost from an IT service due to a major incident. In summary, it represents the maximum amount of data you can afford to lose.
SLES
SUSE Linux Enterprise Server
SPOF
Single Point of Failure
VLDB
Very Large DataBase
WORM
Write Once, Read Many
Database Administrator.
DEB
Debian Package.
External Configuration Files
Files that are stored outside of PGDATA. For example, when you create a new PostgreSQL cluster using pg_createcluster (e.g., sudo pg_createcluster 14 main) in Ubuntu, it sets up a new data directory, typically under /var/lib/postgresql/, but the configuration files, like postgresql.conf and pg_hba.conf, are usually stored under /etc/postgresql/<version>/main/ to separate them from the actual data.
GCP
Google Cloud Platform
GPG
GNU Privacy Guard
IAC
Infrastructure As Code
ICT
Information and Communication Technology.
libpq
The C application programmer’s interface to Postgres. libpq is a set of library functions that allow client programs to pass queries to the Postgres backend server and to receive the results of these queries.
PGDATA
PostgreSQL data directory.
PGDG
Postgres Global Development Group.
PITR
Point-in-time Recovery.
RHEL
Red Hat Enterprise Linux.
RPM
Red Hat Package Manager.
RPO
Recovery Point Objective. The maximum targeted period in which data might be lost from an IT service due to a major incident. In summary, it represents the maximum amount of data you can afford to lose.
SLES
SUSE Linux Enterprise Server
SPOF
Single Point of Failure
VLDB
Very Large DataBase
WORM
Write Once, Read Many
https://raw.githubusercontent.com/postgres/postgres/refs/heads/master/src/include/access/rmgrlist.h
/* there is deliberately not an #ifndef RMGRLIST_H here */
/*
* List of resource manager entries. Note that order of entries defines the
* numerical values of each rmgr's ID, which is stored in WAL records. New
* entries should be added at the end, to avoid changing IDs of existing
* entries.
*
* Changes to this list possibly need an XLOG_PAGE_MAGIC bump.
*/
/* symbol name, textual name, redo, desc, identify, startup, cleanup, mask, decode */
PG_RMGR(RM_XLOG_ID, "XLOG", xlog_redo, xlog_desc, xlog_identify, NULL, NULL, NULL, xlog_decode)
PG_RMGR(RM_XACT_ID, "Transaction", xact_redo, xact_desc, xact_identify, NULL, NULL, NULL, xact_decode)
PG_RMGR(RM_SMGR_ID, "Storage", smgr_redo, smgr_desc, smgr_identify, NULL, NULL, NULL, NULL)
PG_RMGR(RM_CLOG_ID, "CLOG", clog_redo, clog_desc, clog_identify, NULL, NULL, NULL, NULL)
PG_RMGR(RM_DBASE_ID, "Database", dbase_redo, dbase_desc, dbase_identify, NULL, NULL, NULL, NULL)
PG_RMGR(RM_TBLSPC_ID, "Tablespace", tblspc_redo, tblspc_desc, tblspc_identify, NULL, NULL, NULL, NULL)
PG_RMGR(RM_MULTIXACT_ID, "MultiXact", multixact_redo, multixact_desc, multixact_identify, NULL, NULL, NULL, NULL)
PG_RMGR(RM_RELMAP_ID, "RelMap", relmap_redo, relmap_desc, relmap_identify, NULL, NULL, NULL, NULL)
PG_RMGR(RM_STANDBY_ID, "Standby", standby_redo, standby_desc, standby_identify, NULL, NULL, NULL, standby_decode)
PG_RMGR(RM_HEAP2_ID, "Heap2", heap2_redo, heap2_desc, heap2_identify, NULL, NULL, heap_mask, heap2_decode)
PG_RMGR(RM_HEAP_ID, "Heap", heap_redo, heap_desc, heap_identify, NULL, NULL, heap_mask, heap_decode)
PG_RMGR(RM_BTREE_ID, "Btree", btree_redo, btree_desc, btree_identify, btree_xlog_startup, btree_xlog_cleanup, btree_mask, NULL)
PG_RMGR(RM_HASH_ID, "Hash", hash_redo, hash_desc, hash_identify, NULL, NULL, hash_mask, NULL)
PG_RMGR(RM_GIN_ID, "Gin", gin_redo, gin_desc, gin_identify, gin_xlog_startup, gin_xlog_cleanup, gin_mask, NULL)
PG_RMGR(RM_GIST_ID, "Gist", gist_redo, gist_desc, gist_identify, gist_xlog_startup, gist_xlog_cleanup, gist_mask, NULL)
PG_RMGR(RM_SEQ_ID, "Sequence", seq_redo, seq_desc, seq_identify, NULL, NULL, seq_mask, NULL)
PG_RMGR(RM_SPGIST_ID, "SPGist", spg_redo, spg_desc, spg_identify, spg_xlog_startup, spg_xlog_cleanup, spg_mask, NULL)
PG_RMGR(RM_BRIN_ID, "BRIN", brin_redo, brin_desc, brin_identify, NULL, NULL, brin_mask, NULL)
PG_RMGR(RM_COMMIT_TS_ID, "CommitTs", commit_ts_redo, commit_ts_desc, commit_ts_identify, NULL, NULL, NULL, NULL)
PG_RMGR(RM_REPLORIGIN_ID, "ReplicationOrigin", replorigin_redo, replorigin_desc, replorigin_identify, NULL, NULL, NULL, NULL)
PG_RMGR(RM_GENERIC_ID, "Generic", generic_redo, generic_desc, generic_identify, NULL, NULL, generic_mask, NULL)
PG_RMGR(RM_LOGICALMSG_ID, "LogicalMessage", logicalmsg_redo, logicalmsg_desc, logicalmsg_identify, NULL, NULL, NULL, logicalmsg_decode)
https://www.postgresql.org/docs/current/pgwaldump.html
pg_waldump — display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster
pg_waldump — display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster
PostgreSQL Documentation
pg_waldump
pg_waldump pg_waldump — display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster Synopsis pg_waldump [option...] [startseg …
https://www.postgresql.org/docs/current/app-pgresetwal.html
pg_resetwal — reset the write-ahead log and other control information of a PostgreSQL database cluster
pg_resetwal — reset the write-ahead log and other control information of a PostgreSQL database cluster
PostgreSQL Documentation
pg_resetwal
pg_resetwal pg_resetwal — reset the write-ahead log and other control information of a PostgreSQL database cluster Synopsis pg_resetwal [ -f …
Sonia Notes
https://raw.githubusercontent.com/postgres/postgres/refs/heads/master/src/include/access/rmgrlist.h /* there is deliberately not an #ifndef RMGRLIST_H here */ /* * List of resource manager entries. Note that order of entries defines the * numerical values…
/* these variables are GUC parameters related to XLOG */
extern PGDLLIMPORT int wal_segment_size;
extern PGDLLIMPORT int min_wal_size_mb;
extern PGDLLIMPORT int max_wal_size_mb;
extern PGDLLIMPORT int wal_keep_size_mb;
extern PGDLLIMPORT int max_slot_wal_keep_size_mb;
extern PGDLLIMPORT int XLOGbuffers;
extern PGDLLIMPORT int XLogArchiveTimeout;
extern PGDLLIMPORT int wal_retrieve_retry_interval;
extern PGDLLIMPORT char *XLogArchiveCommand;
extern PGDLLIMPORT bool EnableHotStandby;
extern PGDLLIMPORT bool fullPageWrites;
extern PGDLLIMPORT bool wal_log_hints;
extern PGDLLIMPORT int wal_compression;
extern PGDLLIMPORT bool wal_init_zero;
extern PGDLLIMPORT bool wal_recycle;
extern PGDLLIMPORT bool *wal_consistency_checking;
extern PGDLLIMPORT char *wal_consistency_checking_string;
extern PGDLLIMPORT bool log_checkpoints;
extern PGDLLIMPORT int CommitDelay;
extern PGDLLIMPORT int CommitSiblings;
extern PGDLLIMPORT bool track_wal_io_timing;
extern PGDLLIMPORT int wal_decode_buffer_size;
/* Archive modes */
typedef enum ArchiveMode
{
ARCHIVE_MODE_OFF = 0, /* disabled */
ARCHIVE_MODE_ON, /* enabled while server is running normally */
ARCHIVE_MODE_ALWAYS, /* enabled always (even during recovery) */
} ArchiveMode;
extern PGDLLIMPORT int XLogArchiveMode;
/* WAL levels */
typedef enum WalLevel
{
WAL_LEVEL_MINIMAL = 0,
WAL_LEVEL_REPLICA,
WAL_LEVEL_LOGICAL,
} WalLevel;
/* Compression algorithms for WAL */
typedef enum WalCompression
{
WAL_COMPRESSION_NONE = 0,
WAL_COMPRESSION_PGLZ,
WAL_COMPRESSION_LZ4,
WAL_COMPRESSION_ZSTD,
} WalCompression;
/* Recovery states */
typedef enum RecoveryState
{
RECOVERY_STATE_CRASH = 0, /* crash recovery */
RECOVERY_STATE_ARCHIVE, /* archive recovery */
RECOVERY_STATE_DONE, /* currently in production */
} RecoveryState;
extern PGDLLIMPORT int wal_level;
extern PGDLLIMPORT bool XLogLogicalInfo;
/* File path names (all relative to $PGDATA) */
#define RECOVERY_SIGNAL_FILE "recovery.signal"
#define STANDBY_SIGNAL_FILE "standby.signal"
#define BACKUP_LABEL_FILE "backup_label"
#define BACKUP_LABEL_OLD "backup_label.old"
#define TABLESPACE_MAP "tablespace_map"
#define TABLESPACE_MAP_OLD "tablespace_map.old"
/* files to signal promotion to primary */
#define PROMOTE_SIGNAL_FILE "promote"
Schrödinger’s Law of Backups
The condition/state of any backup is unknown until
a restore is attempted.
https://nuventure.medium.com/schrodingers-backup-and-why-you-should-upgrade-your-3-2-1-backup-rule-832decb97d0
—-
• 3 Copies of Data: Maintain three copies of data; the original, and at least two copies.
• 2 Different Media: Use two different media types for storage; This helps to fight off any impacts that can be attributed to a specific type of storage media.
• 1 Copy Offsite: Keep one copy offsite; This prevents the possibility of data loss due to a site-specific failure.
The condition/state of any backup is unknown until
a restore is attempted.
https://nuventure.medium.com/schrodingers-backup-and-why-you-should-upgrade-your-3-2-1-backup-rule-832decb97d0
—-
• 3 Copies of Data: Maintain three copies of data; the original, and at least two copies.
• 2 Different Media: Use two different media types for storage; This helps to fight off any impacts that can be attributed to a specific type of storage media.
• 1 Copy Offsite: Keep one copy offsite; This prevents the possibility of data loss due to a site-specific failure.
Medium
SCHRODINGER’S BACKUP: And Why You Should Upgrade Your 3–2–1 Backup Rule
If you have not heard of Schrödinger’s cat, It is a thought experiment devised by the Austrian physicist Erwin Schrödinger in 1935. As the…
Sonia Notes
https://www.postgresql.org/docs/current/monitoring-stats.html
dynamic statistics view :
View Name Description
pg_stat_activity One row per server process, showing information related to the current activity of that process, such as state and current query. See pg_stat_activity for details.
pg_stat_replication One row per WAL sender process, showing statistics about replication to that sender's connected standby server. See pg_stat_replication for details.
pg_stat_wal_receiver Only one row, showing statistics about the WAL receiver from that receiver's connected server. See pg_stat_wal_receiver for details.
pg_stat_recovery_prefetch Only one row, showing statistics about blocks prefetched during recovery. See pg_stat_recovery_prefetch for details.
pg_stat_subscription At least one row per subscription, showing information about the subscription workers. See pg_stat_subscription for details.
pg_stat_ssl One row per connection (regular and replication), showing information about SSL used on this connection. See pg_stat_ssl for details.
pg_stat_gssapi One row per connection (regular and replication), showing information about GSSAPI authentication and encryption used on this connection. See pg_stat_gssapi for details.
pg_stat_progress_analyze One row for each backend (including autovacuum worker processes) running ANALYZE, showing current progress. See Section 27.4.1.
pg_stat_progress_create_index One row for each backend running CREATE INDEX or REINDEX, showing current progress. See Section 27.4.4.
pg_stat_progress_vacuum One row for each backend (including autovacuum worker processes) running VACUUM, showing current progress. See Section 27.4.5.
pg_stat_progress_cluster One row for each backend running CLUSTER or VACUUM FULL, showing current progress. See Section 27.4.2.
pg_stat_progress_basebackup One row for each WAL sender process streaming a base backup, showing current progress. See Section 27.4.6.
pg_stat_progress_copy One row for each backend running COPY, showing current progress. See Section 27.4.3.