About Lesson
Upon successful installation of your Postgres cluster, the following two things can be checked.
- installation directory
- Cluster directory
It can be checked with below command.
[root@stagdb ~]# ps -ef | grep postgres
postgres 2010 1 0 15:05 pts/4 00:00:00 /usr/local/pgsql/bin/postgres -D /u01/pgsql/data
here, /usr/local/pgsql/ is your PostgreSQL HOME and
/u01/pgsql/data is your data directory.
The following are the four sub folders created under postgres home
| include | Sets the directory for installing C and C++ header files. The default is PREFIX/include. |
| share | Sets the root directory for various types of read-only data files.The default is PREFIX/share. |
| lib | PostgreSQL Library directory refers to the location where the PostgreSQL library files reside. Postgres Lib directory path must contain libpq.so file and other related files. This library is used to connect to the database and execute queries. |
| bin | Specifies the directory for executable programs. The default is EXEC-PREFIX/bin, which normally means /usr/local/pgsql/bin |
Here, bin is the important directiry where the executables are stored. We will try to understand the contents of bin directory in detail.
| executable | Purpose |
| postmaster -> postgres | postmaster process |
| postgres | postmaster process |
| pg_recvlogical | pg_recvlogical controls logical decoding replication slots and streams data from such replication slots. |
| pg_receivewal | pg_receivewal is used to stream the write-ahead log from a running PostgreSQL cluster. The write-ahead log is streamed using the streaming replication protocol, and is written to a local directory of files. This directory can be used as the archive location for doing a restore using point-in-time recovery |
| pg_ctl | controls the postgres cluster |
| pg_controldata | get the pg_control data |
| pg_config | pg_config — retrieve information about the installed version of PostgreSQL |
| pg_basebackup | take physical backup |
| pg_archivecleanup | Clear archivelogs |
| initdb | initialize the database cluster |
| ecpg | ecpg is the embedded SQL preprocessor for C programs. It converts C programs with embedded SQL statements to normal C code by replacing the SQL invocations with special function calls. The output files can then be processed with any C compiler tool chain. |
| vacuumdb | vacuum the database |
| reindexdb | reindexdb is a utility for rebuilding indexes in a PostgreSQL database. |
| psql | connect to postgres database |
| pg_waldump | view the wal file contents |
| pg_upgrade | upgrade your cluster |
| pg_test_timing | pg_test_timing is a tool to measure the timing overhead on your system and confirm that the system time never moves backwards. |
| pg_test_fsync | pg_test_fsync is intended to give you a reasonable idea of what the fastest wal_sync_method is on your specific system, as well as supplying diagnostic information in the event of an identified I/O problem. |
| pg_rewind | rewind the cluster after switch over |
| pg_restore | import logical backup |
| pg_resetwal | reset the wal file |
| pg_isready | pg_isready is a utility for checking the connection status of a PostgreSQL database server. The exit status specifies the result of the connection check. |
| pg_dumpall | used for logical backup or exporting of entire cluster |
| pg_dump | used for logical backup or exporting |
| pgbench | benchmark testing |
| dropuser | drop the user |
| dropdb | drop the database |
| createuser | create the user |
| createdb | create the database |
| clusterdb | clusterdb is a utility for reclustering tables in a PostgreSQL database. It finds tables that have previously been clustered, and clusters them again on the same index that was last used. Tables that have never been clustered are not affected. |
| vacuumlo | vacuumlo is a simple utility program that will remove any “orphaned” large objects from a PostgreSQL database. An orphaned large object (LO) is considered to be any LO whose OID does not appear in any oid or lo data column of the database. |
| pg_standby | pg_standby supports creation of a “warm standby” database server. It is designed to be a production-ready program, as well as a customizable template should you require specific modifications. |
| oid2name | oid2name is a utility program that helps administrators to examine the file structure used by PostgreSQL. |
Data directory layout
| Item | Description |
| PG_VERSION | A file containing the major version number of PostgreSQL |
| base | Subdirectory containing per-database subdirectories |
| current_logfiles | File recording the log file(s) currently written to by the logging collector |
| global | Subdirectory containing cluster-wide tables, such as pg_database |
| pg_commit_ts | Subdirectory containing transaction commit timestamp data |
| pg_dynshmem | Subdirectory containing files used by the dynamic shared memory subsystem |
| pg_logical | Subdirectory containing status data for logical decoding |
| pg_multixact | Subdirectory containing multitransaction status data (used for shared row locks) |
| pg_notify | Subdirectory containing LISTEN/NOTIFY status data |
| pg_replslot | Subdirectory containing replication slot data |
| pg_serial | Subdirectory containing information about committed serializable transactions |
| pg_snapshots | Subdirectory containing exported snapshots |
| pg_stat | Subdirectory containing permanent files for the statistics subsystem |
| pg_stat_tmp | Subdirectory containing temporary files for the statistics subsystem |
| pg_subtrans | Subdirectory containing subtransaction status data |
| pg_tblspc | Subdirectory containing symbolic links to tablespaces |
| pg_twophase | Subdirectory containing state files for prepared transactions |
| pg_wal | Subdirectory containing WAL (Write Ahead Log) files |
| pg_xact | Subdirectory containing transaction commit status data |
| postgresql.auto.conf | A file used for storing configuration parameters that are set by ALTER SYSTEM |
| postmaster.opts | A file recording the command-line options the server was last started with |
| postmaster.pid | A lock file recording the current postmaster process ID (PID), cluster data directory path, postmaster start timestamp, port number, Unix-domain socket directory path (empty on Windows), first valid listen_address (IP address or *, or empty if not listening on TCP), and shared memory segment ID (this file is not present after server shutdown) |