nomadmoms.blogg.se - Postgresql dba tools

POSTGRESQL DBA TOOLS HOW TO
POSTGRESQL DBA TOOLS INSTALL

Alternatives: Pgpool-II, Odyssey, Pgagroal.

Minor and major upgrades using pg_upgrade.

3rd-party: barman, pgbackrest, pg_probackup, WAL-G.

Built-in: pg_dump, pg_dumpall, pg_restore, pg_basebackup.

Replication: streaming replication, logical replication.

POSTGRESQL DBA TOOLS HOW TO

Get practical skills of how to deploy, extend, maintain and support Postgres installations and 3rd-party Postgres ecosystem software.

Advanced topics - row-level security, selinux.

Objects privileges: grant/revoke, default privileges.

Authentication models, roles, pg_hba.conf, SSL settings.

Get understanding about basic security concepts and common ways of how to deploy secure configurations. Keep exploring other configuration options Deep understanding of Postgres internals is not yet necessary here. Get understanding of the main aspects of how Postgres could be configured.

DDL queries: managing tables and schemas (create, alter, drop).

Advanced topics: transactions, CTE, subqueries, lateral join, grouping, set operations.

DML queries: querying data, modifying data, filtering data, joining tables.

Get practical skills of how to create and manipulate database objects and how to execute queries using psql client.

POSTGRESQL DBA TOOLS INSTALL

Official download and install instructions.

Deploy database service in cloud environment (AWS, GCE, Azure, Heroku, DigitalOcean, etc.).

Managing Postgres service using pg_ctl, or OS-specific tools (like pg_ctlcluster).

Managing Postgres service using systemd (start, stop, restart, reload).

Using package managers (APT, YUM, etc.).

Get practical skills of how to set up and run Postgres to get a working environment for further learning.

Database Design and Relational Theory - Christopher J.

SQL and Relational Theory - Christopher J.

Databases high-level concepts: ACID, MVCC, transactions, write-ahead log, query processing.

Relational model: domains, attributes, tuples, relations, constraints, NULL.

Object model: data types, columns, rows, tables, schemas, databases, queries.

Get basic understanding of Postgres key terms and basic RDBMS concepts. The roadmap is highly opinionated - neither, knowing everything listed in the roadmap, nor the order of items given in the roadmap is required to be followed in order to be a DBA. The intent of this guide is to give you an idea about the DBA landscape and to help guide your learning if you are confused.