Sonia Notes
92 subscribers
99 photos
1 video
10 files
142 links
Download Telegram
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.
iterative vs recurssive dns
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.
Sonia Notes
https://www.postgresql.org/docs/current/monitoring-stats.html
collected statistics view

pg_stat_archiver  One row only, showing statistics about the WAL archiver process's activity. See pg_stat_archiver for details.
pg_stat_bgwriter One row only, showing statistics about the background writer process's activity. See pg_stat_bgwriter for details.
pg_stat_checkpointer One row only, showing statistics about the checkpointer process's activity. See pg_stat_checkpointer for details.
pg_stat_database One row per database, showing database-wide statistics. See pg_stat_database for details.
pg_stat_database_conflicts One row per database, showing database-wide statistics about query cancels due to conflict with recovery on standby servers. See pg_stat_database_conflicts for details.
pg_stat_io One row for each combination of backend type, context, and target object containing cluster-wide I/O statistics. See pg_stat_io for details.
pg_stat_replication_slots One row per replication slot, showing statistics about the replication slot's usage. See pg_stat_replication_slots for details.
pg_stat_slru One row per SLRU, showing statistics of operations. See pg_stat_slru for details.
pg_stat_subscription_stats One row per subscription, showing statistics about errors and conflicts. See pg_stat_subscription_stats for details.
pg_stat_wal One row only, showing statistics about WAL activity. See pg_stat_wal for details.
pg_stat_all_tables One row for each table in the current database, showing statistics about accesses to that specific table. See pg_stat_all_tables for details.
pg_stat_sys_tables Same as pg_stat_all_tables, except that only system tables are shown.
pg_stat_user_tables Same as pg_stat_all_tables, except that only user tables are shown.
pg_stat_xact_all_tables Similar to pg_stat_all_tables, but counts actions taken so far within the current transaction (which are not yet included in pg_stat_all_tables and related views). The columns for numbers of live and dead rows and vacuum and analyze actions are not present in this view.
pg_stat_xact_sys_tables Same as pg_stat_xact_all_tables, except that only system tables are shown.
pg_stat_xact_user_tables Same as pg_stat_xact_all_tables, except that only user tables are shown.
pg_stat_all_indexes One row for each index in the current database, showing statistics about accesses to that specific index. See pg_stat_all_indexes for details.
pg_stat_sys_indexes Same as pg_stat_all_indexes, except that only indexes on system tables are shown.
pg_stat_user_indexes Same as pg_stat_all_indexes, except that only indexes on user tables are shown.
pg_stat_user_functions One row for each tracked function, showing statistics about executions of that function. See pg_stat_user_functions for details.
pg_stat_xact_user_functions Similar to pg_stat_user_functions, but counts only calls during the current transaction (which are not yet included in pg_stat_user_functions).
pg_statio_all_tables One row for each table in the current database, showing statistics about I/O on that specific table. See pg_statio_all_tables for details.
pg_statio_sys_tables Same as pg_statio_all_tables, except that only system tables are shown.
pg_statio_user_tables Same as pg_statio_all_tables, except that only user tables are shown.
pg_statio_all_indexes One row for each index in the current database, showing statistics about I/O on that specific index. See pg_statio_all_indexes for details.
pg_statio_sys_indexes Same as pg_statio_all_indexes, except that only indexes on system tables are shown.
pg_statio_user_indexes Same as pg_statio_all_indexes, except that only indexes on user tables are shown.
pg_statio_all_sequences One row for each sequence in the current database, showing statistics about I/O on that specific sequence. See pg_statio_all_sequences for details.
pg_statio_sys_sequences Same as pg_statio_all_sequences, except that only system sequences are shown. (Presently, no system sequences are defined, so this view is always empty.)
pg_statio_user_sequences Same as pg_statio_all_sequences, except that only user sequences are shown.
How is AI used in Databasus development?
There have been questions about AI usage in project development in issues and discussions. As the project focuses on security, reliability and production usage, it's important to explain how AI is used in the development process.

AI is used as a helper for:

Verification of code quality and searching for vulnerabilities
Cleaning up and improving documentation, comments and code
Assistance during development
Double-checking PRs and commits after human review
AI is NOT used for:

Writing entire code
"Vibe code" approach
Code without line-by-line verification by a human
Code without tests
The project has:

Solid test coverage (both unit and integration tests)
CI/CD pipeline automation with tests and linting to ensure code quality
Verification by experienced developers with experience in large and secure projects
So AI is just an assistant and a tool for developers to increase productivity and ensure code quality. The work is done by developers.
The practitioner may report on any of the trust services categories of security, availability, processing integ-
rity, confidentiality, or privacy, either individually or in combination with one or more of the other trust
services categories. For each category addressed by the engagement, all criteria for that category are
usually addressed. However, in limited circumstances, such as when the scope of the engagement is to
report on a system and a particular criterion is not relevant to the services provided by a service organi-
zation, one or more criteria may not be applicable to the engagement. For example, when reporting on privacy for a service organization’s system, criterion P3.1, Personal information is collected consistent
with the entity’s objectives related to privacy, is not applicable for a service organization that does not
directly collect personal information from data subjects.
👍1
Security. Information and systems are protected against unauthorized access, unauthorized dis-
closure of information, and damage to systems that could compromise the availability, integrity,
confidentiality, and privacy of information or systems and affect the entity’s ability to achieve its
objectives.

Availability. Information and systems are available for operation and use to meet the entity’s ob-
jectives.

Processing integrity (over the provision of services or the production, manufacturing, or distri-
bution of goods). System processing is complete, valid, accurate, timely, and authorized to meet
the entity’s objectives.

Confidentiality. Information designated as confidential is protected to meet the entity’s objec-
e. tives.

Privacy. Personal information is collected, used, retained, disclosed, and disposed of to meet the
entity’s objectives.
😁1
In an entity-wide SOC for Cybersecurity examination, the entity establishes cybersecurity objec-
tives. Cybersecurity objectives are those that could be affected by cybersecurity risk and, there-
fore, affect the achievement of the entity’s compliance, reporting, and operational objectives.
The nature of an entity’s cybersecurity objectives will vary depending on the environment in
which the entity operates, the entity’s mission and vision, the overall business objectives estab-
lished by management, and other factors. For example, a telecommunication entity may have a
cybersecurity objective related to the reliable functioning of those aspects of its operations that
are deemed to be critical infrastructure, whereas an online dating entity is likely to regard the
privacy of the personal information collected from customers to be a critical factor in achieving
its operating objectives. fn 7
🥰1
COSO Principle 1: The entity demonstrates a commitment to integrity and ethical values.

COSO Principle 2: The board of directors demonstrates independence from management and
exercises oversight of the development and performance of internal control.

COSO Principle 3: Management establishes, with board oversight, structures, reporting lines,
and appropriate authorities and responsibilities in the pursuit of objectives.

COSO Principle 4: The entity demonstrates a commitment to attract, develop, and retain com-
petent individuals in alignment with objectives.

COSO Principle 5: The entity holds individuals accountable for their internal control responsi-
bilities in the pursuit of objectives.

COSO Principle 6: The entity specifies objectives with sufficient clarity to enable the identifica-
tion and assessment of risks relating to objectives.

COSO Principle 7: The entity identifies risks to the achievement of its objectives across the enti-
ty and analyzes risks as a basis for determining how the risks should be managed.

COSO Principle 8: The entity considers the potential for fraud in assessing risks to the
achievement of objectives.

COSO Principle 9: The entity identifies and assesses changes that could significantly impact the
system of internal control.

COSO Principle 10: The entity selects and develops control activities that contribute to the mit-
igation of risks to the achievement of objectives to acceptable levels.

COSO Principle 11: The entity also selects and develops general control activities over technol-
ogy to support the achievement of objectives.

COSO Principle 12: The entity deploys control activities through policies that establish what is
expected and in procedures that put policies into action.

COSO Principle 13: The entity obtains or generates and uses relevant, quality information to
support the functioning of internal control.

COSO Principle 14: The entity internally communicates information, including objectives and
responsibilities for internal control, necessary to support the functioning of internal control.

COSO Principle 15: The entity communicates with external parties regarding matters affecting
the functioning of internal control.

COSO Principle 16: The entity selects, develops, and performs ongoing and/or separate evalua-
tions to ascertain whether the components of internal control are present and functioning.

COSO Principle 17: The entity evaluates and communicates internal control deficiencies in a
timely manner to those parties responsible for taking corrective action, including senior man-
agement and the board of directors, as appropriate.
👍1
Change Management :

Manages Changes Throughout the System Life Cycle — A process for managing
system changes throughout the life cycle of the system and its components (infra-
structure, data, software, and procedures) is used to support system availability and
processing integrity.

• Authorizes Changes — A process is in place to authorize system changes prior to
development.

• Designs and Develops Changes — A process is in place to design and develop sys-
tem changes.

• Documents Changes — A process is in place to document system changes to sup-
port ongoing maintenance of the system and to support system users in performing
their responsibilities.

• Tracks System Changes — A process is in place to track system changes prior to
implementation.

• Configures Software — A process is in place to select and implement the configura-
tion parameters used to control the functionality of software.

• Tests System Changes — A process is in place to test system changes prior to im-
plementation.

• Approves System Changes — A process is in place to approve system changes prior
to implementation.

• Deploys System Changes — A process is in place to implement system changes.
• Identifies and Evaluates System Changes — Objectives affected by system changes
are identified and the ability of the modified system to meet the objectives is evalu-
ated throughout the system development life cycle.

• Identifies Changes in Infrastructure, Data, Software, and Procedures Required to
Remediate Incidents — Changes in infrastructure, data, software, and procedures
required to remediate incidents to continue to meet objectives are identified and the
change process is initiated upon identification.
SOC 2 engagement
An examination engagement to report on the fairness of the presentation of
management’s description of the service organization’s system, the suitability of the design of
the controls included in the description, and, in a type 2 engagement, the operating effectiveness
of those controls. This engagement is performed in accordance with the attestation standards and
AICPA Guide SOC 2® Reporting on an Examination of Controls at a Service Organization: Rel-
evant to Security, Availability, Processing Integrity, Confidentiality, or Privacy.

SOC 3 engagement
An examination engagement to report on the suitability of design and the oper-
ating effectiveness of an entity’s controls over a system relevant to one or more of the trust ser-
vices categories.

SOC for Cybersecurity examination
An examination engagement to report on whether (a) man-
agement’s description of the entity’s cybersecurity risk management program is presented in ac-
cordance with the description criteria and (b) the controls within that program were effective to
achieve the entity’s cybersecurity objectives based on the control criteria. A SOC for Cybersecu-
rity examination is performed in accordance with the attestation standards and AICPA Guide
Reporting on an Entity's Cybersecurity Risk Management Program and Controls.

SOC for Supply Chain examination
An examination engagement to report on whether (a) the de-
scription of the entity’s system is presented in accordance with the description criteria and (b) the
controls stated in the description, which are necessary to provide reasonable assurance that the
entity achieved its principal system objectives, were effective based on the applicable trust ser-
vices criteria. Such an examination is based on guidance contained in AICPA Guide SOC for
Supply Chain: Reporting on an Examination of Controls Relevant to Security, Availability, Pro-
cessing Integrity, Confidentiality, or Privacy in a Production, Manufacturing, or Distribution
System.
The AICPA’s Assurance Services Executive Committee (ASEC), through its Trust Information Integrity
Task Force’s SOC 2 Working Group, has developed a set of benchmarks, known as description criteria,
to be used when preparing and evaluating the description of the service organization’s system (descrip-
tion) in an examination of a service organization’s controls over security, availability, processing integ-
rity, confidentiality, or privacy (SOC 2 examination). This document presents the description criteria for
use in that examination. (This document does not address the AICPA’s trust services criteria, fn 3 which
are used in a SOC 2 examination to evaluate whether controls stated in the description were suitably de-
signed and operated effectively to provide reasonable assurance that the service organization’s service
commitments and system requirements were achieved based on the applicable trust services criteria.)