Greenplum Database File Structure

Master Data Directory
[gpadmin@sachi gpsne0]$ cd $MASTER_DATA_DIRECTORY
[gpadmin@sachi gpsne-1]$ ls
base     gpperfmon          pg_distributedlog     pg_ident.conf  pg_stat_tmp  pg_twophase            pg_xlog          postmaster.pid
global   pg_changetracking  pg_distributedxidmap  pg_log         pg_subtrans  pg_utilitymodedtmredo  postgresql.conf
gp_dbid  pg_clog            pg_hba.conf           pg_multixact   pg_tblspc    PG_VERSION             postmaster.opts
[gpadmin@sachi gpsne-1]$ pwd
/home/gpmaster/gpsne-1
[gpadmin@sachi gpsne-1]$ ll
total 116
drwx------. 8 gpadmin gpadmin  4096 Mar 13 22:19 base
drwx------. 2 gpadmin gpadmin  4096 Mar 13 22:19 global
drwxrwxr-x. 5 gpadmin gpadmin  4096 Nov 27 12:51 gpperfmon
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:50 pg_changetracking
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:50 pg_clog
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:50 pg_distributedlog
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:50 pg_distributedxidmap
drwx------. 2 gpadmin gpadmin 12288 Mar 15 00:00 pg_log
drwx------. 4 gpadmin gpadmin  4096 Nov 27 12:50 pg_multixact
drwx------. 2 gpadmin gpadmin  4096 Mar 10 16:56 pg_stat_tmp
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:50 pg_subtrans
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:50 pg_tblspc
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:50 pg_twophase
drwx------. 2 gpadmin gpadmin  4096 Jan 18 02:15 pg_utilitymodedtmredo
drwx------. 3 gpadmin gpadmin  4096 Nov 27 12:50 pg_xlog
-rw-rw-r--. 1 gpadmin gpadmin  4751 Mar 10 16:56 pg_hba.conf
-rw-------. 1 gpadmin gpadmin  1636 Nov 27 12:50 pg_ident.conf
-rw-------. 1 gpadmin gpadmin     4 Nov 27 12:50 PG_VERSION
-r--------. 1 gpadmin gpadmin   109 Nov 27 12:51 gp_dbid
-rw-------. 1 gpadmin gpadmin 19299 Nov 27 12:51 postgresql.conf
-rw-------. 1 gpadmin gpadmin   169 Mar 10 16:56 postmaster.opts
-rw-------. 1 gpadmin gpadmin    48 Mar 10 16:56 postmaster.pid
[gpadmin@sachi gpsne-1]$ 

Note: 15 Directories and 7 files
Segment Data Directory

[gpadmin@sachi gpsne1]$ cd /disk1/gpdata1/gpsne0
[gpadmin@sachi gpsne0]$ ll
total 104
drwx------. 8 gpadmin gpadmin  4096 Mar 13 22:19 base
drwx------. 2 gpadmin gpadmin  4096 Mar 13 22:19 global
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_changetracking
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_clog
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_distributedlog
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_distributedxidmap
drwx------. 2 gpadmin gpadmin  4096 Mar 15 00:00 pg_log
drwx------. 4 gpadmin gpadmin  4096 Nov 27 12:51 pg_multixact
drwx------. 2 gpadmin gpadmin  4096 Mar 10 16:56 pg_stat_tmp
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_subtrans
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_tblspc
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_twophase
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_utilitymodedtmredo
drwx------. 3 gpadmin gpadmin  4096 Nov 27 12:51 pg_xlog
-rw-------. 1 gpadmin gpadmin     4 Nov 27 12:51 PG_VERSION
-rw-------. 1 gpadmin gpadmin  4529 Nov 27 12:54 pg_hba.conf
-rw-------. 1 gpadmin gpadmin  1636 Nov 27 12:51 pg_ident.conf
-r--------. 1 gpadmin gpadmin   109 Nov 27 12:54 gp_dbid
-rw-------. 1 gpadmin gpadmin 19205 Nov 27 12:54 postgresql.conf
-rw-------. 1 gpadmin gpadmin   158 Mar 10 16:56 postmaster.opts
-rw-------. 1 gpadmin gpadmin    47 Mar 10 16:56 postmaster.pid
[gpadmin@sachi gpsne0]$ pwd
/disk1/gpdata1/gpsne0
[gpadmin@sachi gpsne0]$ cd /disk2/gpdata2/gpsne1
[gpadmin@sachi gpsne1]$ ls -l
total 104
drwx------. 8 gpadmin gpadmin  4096 Mar 13 22:19 base
drwx------. 2 gpadmin gpadmin  4096 Mar 13 22:19 global
-r--------. 1 gpadmin gpadmin   109 Nov 27 12:54 gp_dbid
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_changetracking
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_clog
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_distributedlog
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_distributedxidmap
-rw-------. 1 gpadmin gpadmin  4529 Nov 27 12:54 pg_hba.conf
-rw-------. 1 gpadmin gpadmin  1636 Nov 27 12:51 pg_ident.conf
drwx------. 2 gpadmin gpadmin  4096 Mar 15 00:00 pg_log
drwx------. 4 gpadmin gpadmin  4096 Nov 27 12:51 pg_multixact
drwx------. 2 gpadmin gpadmin  4096 Mar 10 16:56 pg_stat_tmp
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_subtrans
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_tblspc
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_twophase
drwx------. 2 gpadmin gpadmin  4096 Nov 27 12:51 pg_utilitymodedtmredo
-rw-------. 1 gpadmin gpadmin     4 Nov 27 12:51 PG_VERSION
drwx------. 3 gpadmin gpadmin  4096 Nov 27 12:51 pg_xlog
-rw-------. 1 gpadmin gpadmin 19205 Nov 27 12:54 postgresql.conf
-rw-------. 1 gpadmin gpadmin   158 Mar 10 16:56 postmaster.opts
-rw-------. 1 gpadmin gpadmin    47 Mar 10 16:56 postmaster.pid
[gpadmin@sachi gpsne1]$ 


 Contents of PGDATA

ItemDescription
PG_VERSIONA file containing the major version number of PostgreSQL
baseSubdirectory containing per-database subdirectories
globalSubdirectory containing cluster-wide tables, such as pg_database
pg_clogSubdirectory containing transaction commit status data
pg_multixactSubdirectory containing multitransaction status data (used for shared row locks)
pg_notifySubdirectory containing LISTEN/NOTIFY status data
pg_stat_tmpSubdirectory containing temporary files for the statistics subsystem
pg_subtransSubdirectory containing subtransaction status data
pg_tblspcSubdirectory containing symbolic links to tablespaces
pg_twophaseSubdirectory containing state files for prepared transactions
pg_xlogSubdirectory containing WAL (Write Ahead Log) files
postmaster.optsA file recording the command-line options the server was last started with
postmaster.pidA lock file recording the current server PID and shared memory segment ID (not present after server shutdown)

[gpadmin@sachi gpsne-1]$ cd base
[gpadmin@sachi base]$ ls
1  10899  10900  16992  16993  33476  33553

Note: 7 Directories

sachi=# select * from pg_database;
  datname  | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig |     datacl                                 
-----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+------------------------------------------------------------------------
 gpadmin   |     10 |        6 | f             | t            |           -1 |         10899 |          803 |          1663 |           | 
 postgres  |     10 |        6 | t             | t            |           -1 |         10899 |          803 |          1663 |           | 
 template1 |     10 |        6 | t             | t            |           -1 |         10899 |          803 |          1663 |           | {=c/gpadmin,gpadmin=CTc/gpadmin}
 template0 |     10 |        6 | t             | f            |           -1 |         10899 |          803 |          1663 |           | {=c/gpadmin,gpadmin=CTc/gpadmin}
 himanshu  |     10 |        6 | f             | t            |           -1 |         10899 |          803 |          1663 |           | 
 sachi     |     10 |        6 | f             | t            |           -1 |         10899 |          803 |          1663 |           | {=Tc/gpadmin,gpadmin=CTc/gpadmin,sachi=CTc/gpadmin,gpuser=CTc/gpadmin}
 gpperfmon |     10 |        6 | f             | t            |           -1 |         10899 |          803 |          1663 |           | {gpadmin=CTc/gpadmin,=c/gpadmin}
(7 rows)

Pg_database table
NameTypeReferencesDescription
datnamename Database name
datdbaoidpg_authid.oidOwner of the database, usually the user who created it
encodingint4 Character encoding for this database (pg_encoding_to_char() can translate this number to the encoding name)
datcollatename LC_COLLATE for this database
datctypename LC_CTYPE for this database
datistemplatebool If true then this database can be used in the TEMPLATE clause of CREATE DATABASE to create a new database as a clone of this one
datallowconnbool If false then no one can connect to this database. This is used to protect the template0 database from being altered.
datconnlimitint4 Sets maximum number of concurrent connections that can be made to this database. -1 means no limit.
datlastsysoidoid Last system OID in the database; useful particularly to pg_dump
datfrozenxidxid All transaction IDs before this one have been replaced with a permanent ("frozen") transaction ID in this database. This is used to track whether the database needs to be vacuumed in order to prevent transaction ID wraparound or to allow pg_clog to be shrunk. It is the minimum of the per-tablepg_class.relfrozenxid values.
dattablespaceoidpg_tablespace.oidThe default tablespace for the database. Within this database, all tables for which pg_class.reltablespace is zero will be stored in this tablespace; in particular, all the non-shared system catalogs will be there.
dataclaclitem[] Access privileges; see GRANT and REVOKE for details

System Catalogs

Catalog NamePurpose
pg_aggregateaggregate functions
pg_amindex access methods
pg_amopaccess method operators
pg_amprocaccess method support procedures
pg_attrdefcolumn default values
pg_attributetable columns ("attributes")
pg_authidauthorization identifiers (roles)
pg_auth_membersauthorization identifier membership relationships
pg_castcasts (data type conversions)
pg_classtables, indexes, sequences, views ("relations")
pg_constraintcheck constraints, unique constraints, primary key constraints, foreign key constraints
pg_conversionencoding conversion information
pg_databasedatabases within this database cluster
pg_db_role_settingper-role and per-database settings
pg_default_acldefault privileges for object types
pg_dependdependencies between database objects
pg_descriptiondescriptions or comments on database objects
pg_enumenum label and value definitions
pg_foreign_data_wrapperforeign-data wrapper definitions
pg_foreign_serverforeign server definitions
pg_indexadditional index information
pg_inheritstable inheritance hierarchy
pg_languagelanguages for writing functions
pg_largeobjectdata pages for large objects
pg_largeobject_metadatametadata for large objects
pg_namespaceschemas
pg_opclassaccess method operator classes
pg_operatoroperators
pg_opfamilyaccess method operator families
pg_pltemplatetemplate data for procedural languages
pg_procfunctions and procedures
pg_rewritequery rewrite rules
pg_shdependdependencies on shared objects
pg_shdescriptioncomments on shared objects
pg_statisticplanner statistics
pg_tablespacetablespaces within this database cluster
pg_triggertriggers
pg_ts_configtext search configurations
pg_ts_config_maptext search configurations' token mappings
pg_ts_dicttext search dictionaries
pg_ts_parsertext search parsers
pg_ts_templatetext search templates
pg_typedata types
pg_user_mappingmappings of users to foreign servers
Comments