pg_controldata

-

 

pg_controldata

A server utility to show global state information for a database cluster

pg_controldata is a server utility which displays global state information about a database cluster stored in the global/pg_control file. This information includes cluster characteristics fixed during the initdb process such as blocksize as well as WAL / checkpoint-related data and data page checksums.

pg_controldata is included with the PostgreSQL core distribution; prior to PostgreSQL 7.3 it was a contrib module.

SQL alternatives

From PostgreSQL 9.6 the contents of pg_control can be directly queried with the following functions:

Change history

Examples

Example output:

$ pg_controldata -D /path/to/pgdata
pg_control version number:            1201
Catalog version number:               201909212
Database system identifier:           6872135480623396628
Database cluster state:               in archive recovery
pg_control last modified:             Mon 14 Sep 2020 09:35:29 AM UTC
Latest checkpoint location:           0/2000060
Latest checkpoint's REDO location:    0/2000028
Latest checkpoint's REDO WAL file:    000000010000000000000002
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:495
Latest checkpoint's NextOID:          24576
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        479
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  495
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Mon 14 Sep 2020 09:35:28 AM UTC
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/2000100
Min recovery ending loc's timeline:   1
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                on
max_connections setting:              20
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           10
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           1
Mock authentication nonce:            f5f96b5d2aee1c18e95333fb089ac55415aa1aef63158456fdcd09044f00bb32

Comments

Post a Comment

Popular posts from this blog

PostgreSQL - Architecture

-
Image
When you start PostgreSQL`s instance (via pg_ctl start) the main process of the databases is started. In the past versions it was known as the postmaster process and now days it is called postgres. When this processes is started it allocates memory for two main things : shared memory and other background processes. The shared memory is a memory that the database uses mainly for cashing. Its main goal is to reduce the I/O operations and as a result of that improve the performance of the database. The other processes that the postgres process starts are responsible for the ongoing work of the database.  PostgreSQL`s architecture is based on a client/server communication. In the server side there is a server process that manages the files, database operations, connection and so on.. On the client side there is a tool/program which can be a graphical tool (gui)  like pgAdmin or a command line tool(cli) like psql  that is repnsible for connection the database and allowing the user
Read more

PostgreSQL - Backup & Point In Time Recovery

-
Image
  In this post you will learn about how to restore a PostgreSQL database in Point in time. Open source databases are taking over other conventional Database softwares at very high speed. For me personally, I really like the way PostgreSQL is designed and it’s simple architecture. I have tried to write it down every possible steps which will help you to restore your Postgres database. Below Steps will be same, but few commands may vary with any other backup tool. PITR PostgreSQL Allow you to restore database to a specific moment in time PITR. It make use of live database files and WAL files. This method can only backup and restore the whole cluster, for individual database use pg_dump. It can be done when the DB is online. Step 1. If archive logs not enable then enable it, otherwise go to Step3. Create Directory for archive logs mkdir /postgres/postgres12.2/wal_archives Make changes in postgresql.conf Postgresql.conf is present under your data directory, in my case it is in /postgres/po
Read more

Installing pgbadger on Linux for PostgreSQL log analysis

-
As a DBA, we often encounter scenarios when you need to track PostgreSQL database performance by analyzing workloads and identify bottlenecks if any. There comes  pgbadger  – an open source log analyzer for PostgreSQL. It parse the PostgreSQL log files and gives you a nice overview of the PostgreSQL cluster including various query metrics like number of query executions, their types and duration, session details, Locks statistics, warnings, errors and other event patterns etc. All of these details will be available in a nice HTML format on your web browser. Installation: Prerequisite: The  pgbadger  is written in pure  Perl  and uses a JavaScript library ( flotr2 ) to draw graphs. Hence, you need to ensure that a modern Perl distribution is available in your system. Charts are rendered using a JavaScript library and Your web browser will do all the work. Nothing additional is required here. Use the below command to install  perl  if it is not already installed in your system. $ yum ins
Read more