Postgres: Where are my Postgres files ?


My config files
select name, setting from pg_settings where category='File Locations';

       name        |                       setting
-------------------+------------------------------------------------------
config_file       | /var/vms/vp/current/pgdata/postgresql.conf
data_directory    | /var/vms/vp/current/pgdata
external_pid_file |
hba_file          | /var/vms/vp/current/pgdata/pg_hba.conf
ident_file        | /var/vms/vp/current/pgdata/pg_ident.conf
(5 rows)

Some interesting parameters for Perf Tuning.

select name, context, unit, setting from pg_settings where name in ('listen_address','max_connections','shared_buffers','effective_cache_size','work_mem','maintenance_work_mem') order by context, name;

         name         |  context   | unit | setting
----------------------+------------+------+---------
max_connections      | postmaster |      | 2000
shared_buffers       | postmaster | 8kB  | 270208
effective_cache_size | user       | 8kB  | 16384
maintenance_work_mem | user       | kB   | 153600
work_mem             | user       | kB   | 77

###postmmaster means changing the value require db bounce.


pg_controldata - acts like Oracle controlfile .. sorta
./usr/pgsql-9.3/bin/pg_controldata


Do not mess with this file. DB will not started backup.  As you can see, the type is an executable.

-bash-4.1$ file ./usr/pgsql-9.3/bin/pg_controldata
./usr/pgsql-9.3/bin/pg_controldata: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.18, stripped

Details of controlfile.

-bash-4.1$ ./usr/pgsql-9.3/bin/pg_controldata

pg_control version number:            937
Catalog version number:               201306121
Database system identifier:           9094247372522568574
Database cluster state:               in production
pg_control last modified:             Tue 20 Jan 2015 05:54:03 PM EST
Latest checkpoint location:           0/1C781F8
Prior checkpoint location:            0/1A08C68
Latest checkpoint's REDO location:    0/1C781F8
Latest checkpoint's REDO WAL file:    000000010000000000000001
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/2074
Latest checkpoint's NextOID:          24576
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1799
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Tue 20 Jan 2015 05:53:25 PM EST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            minimal
Current max_connections setting:      100
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
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
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0


My postgres archivelogs details

VCDB=# select name, context, unit, setting from pg_settings where name like '%archive%';
           name            |  context   | unit |                          setting
---------------------------+------------+------+-----------------------------------------------------------
archive_command           | sighup     |      | /opt/vms/vp/current/share/archive_command %p %f
archive_mode              | postmaster |      | on
archive_timeout           | sighup     | s    | 0
max_standby_archive_delay | sighup     | ms   | 30000
(4 rows)



postgres=#  select name, context, unit, setting from pg_settings where name like '%level%';
   name    |  context   | unit | setting
-----------+------------+------+---------
wal_level | postmaster |      | minimal
(1 row)

No comments:

Post a Comment