PostgreSQL for Oracle DBAs

Introduction

The following article contains information to help an Oracle DBA understand some terms and the management of a PostgreSQL database. This article is intended to be an introduction to PostgreSQL, not a tutorial or a complete definition of how to administer a PostgreSQL database. For complete documentation refer to the PostgreSQL manuals.

Oracle

Brief description:

• An Oracle database server consists of an Oracle instance and an Oracle database.
• An Oracle instance consists of the Oracle background processes and the allocated memory within the shared global area (SGA) and the program global area (PGA).
• The important Oracle background processes are the following:
  • Database Writer Process (DBWn)
  • Log Writer Process (LGWR)
  • Checkpoint Process (CKPT)
  • System Monitor Process (SMON)
  • Process Monitor Process (PMON)
  • Recoverer Process (RECO)
  • Archiver Processes (ARCn)
• An Oracle database consists of the database datafiles, control files, redo log files, archive log files, and parameter file. This may be files on a local file system, NFS files or SAN Devices. In the latter case Oracle uses ASM (Automatic Storage Management) to address data.
• To remotely access an Oracle database, there exists a separate process referred to as the Oracle listener.
• In the Dedicated Server configuration (versus the Shared Server configuration) every established database session has its own process executing on the server.

To keep things simple any comparisons with an Oracle database will always refer to a single instance managing a single database, RAC and Data Guard will not be mentioned. Note: PostgreSQL also has the concept of a warm standby (since 8.2) with the shipping of archive logs (introduced in 8.0).

PostgreSQL

Database Server Processes

The database server program postgres are all of the server processes. There are no separately named processes like in Oracle for the different duties within the database environment. If you were to look at the process list (ps) the name of the processes would be postgres. However, on most platforms, PostgreSQL modifies its command title so that individual server processes can readily be identified. You may need to adjust the parameters used for commands such as ps and top to show these updated titles in place of the process name ("postgres").

The processes seen in a process list can be some of the following:

• Master process - launches the other processes, background and session processes.
• Writer process - background process that coordinates database writes, log writes and checkpoints.
• Stats collector process - background process collecting information about server activity.
• User session processes.

The server processes communicate with each other using semaphores and shared memory to ensure data integrity throughout concurrent data access.

PostgreSQL Database Cluster

Within a server, one or more Oracle instances can be built. The databases are separate from one another usually sharing only the Oracle listener process. PostgreSQL has the concept of a database cluster. A database cluster is a collection of databases that is stored at a common file system location (the "data area"). It is possible to have multiple database clusters, so long as they use different data areas and different communication ports.

The processes along with the file system components are all shared within the database cluster. All the data needed for a database cluster is stored within the cluster's data directory, commonly referred to as PGDATA (after the name of the environment variable that can be used to define it). The PGDATA directory contains several subdirectories and configuration files.

The following are some of the cluster configuration files:

• postgresql.conf - Parameter or main server configuration file.
• pg_hba.conf - Client authentication configuration file.
• pg_ident.conf - Map from OS account to PostgreSQL account file.

The cluster subdirectories:

• base - Subdirectorycontaining per-database subdirectories
• global - Subdirectory containing cluster-wide tables
  • pg_auth - Authorization file containing user and role definitions.
  • pg_control - Control file.
  • pg_database - Information of databases within the cluster.
• pg_clog - Subdirectory containing transaction commit status data
• pg_multixact - Subdirectory containing multitransaction status data (used for shared row locks)
• 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_xlog - Subdirectory containing WAL (Write Ahead Log) files

By default, for each database in the cluster there is a subdirectory within PGDATA/base, named after the database's OID (object identifier) in pg_database. This subdirectory is the default location for the database's files; in particular, its system catalogs are stored there. Each table and index is stored in a separate file, named after the table or index's filenode number, which can be found in pg_class.relfilenode.

Several components that Oracle DBAs usually equate to one database are shared between databases within a PostgreSQL cluster, including the parameter file, control file, redo logs, tablespaces, accounts, roles, and background processes.

Tablespaces and Object Data Files

PostgreSQL introduced tablespace management in version 8.0. The physical representation of a tablespace within PostgreSQL is simple: it is a directory on the file system, and the mapping is done via symbolic links.

When a database is created, the default tablespace is where by default all of the database objects are stored. In Oracle this would be similar to the System, User, and Temporary tablespaces. If no default tablespace is defined during creation, the data files will go into a subdirectory of the PGDATA/base. Preferably the location of the system catalog information and the application data structures would reside in separately managed tablespaces. This is available.

As in Oracle, the definition of a PostgreSQL table determines which tablespace the object resides. However, there exists no size limitation except physical boundaries placed on the device by the OS.

The individual table's data is stored within a file within the tablespace (or directory). The database software will split the table across multiple datafiles in the event the table's data surpasses 1 GB.

Since version 8.1, it's possible to partition a table over separate (or the same) tablespaces. This is based on PostgreSQL's table inheritance feature, using a capability of the query planner referred to as constraint exclusion.

There exists no capacity for separating out specific columns (like LOBs) into separately defined tablespaces. However, in addition to the data files that represent the table (in multiples of 1 GB) there is a separation of data files for columns within a table that are TOASTed. The PostgreSQL storage system called TOAST (The Oversized-Attribute Storage Technique) automatically stores values larger than a single database page into a secondary storage area per table. The TOAST technique allows for data columns up to 1 GB in size.

As in Oracle, the definition of an index determines which tablespace it resides within. Therefore, it is possible to gain the performance advantage of separating the disks that a table's data versus its indexing reside, relieving I/O contention during data manipulation.

In Oracle there exists temporary tablespaces where sort information and temporary evaluation space needed for distinct statements and the like are used. PostgreSQL does not have this concept of a temporary tablespace; however it does require storage to be able to perform these activities as well. Within the "default" tablespace of the database (defined at database creation) there is a directory called pgsql_tmp. This directory holds the temporary storage needed for the evaluation. The files that get created within the directory exist only while the SQL statement is executing. They grow very fast, and are most likely not designed for space efficiency but rather speed. Be aware that disk fragmentation could result from this, and there needs to be sufficient space on the disk to support the user queries. With the release of 8.3, there are definitions of temporary tablespaces using the parameter temp_tablespaces.

REDO and Archiving

PostgreSQL uses Write-Ahead Logging (WAL) as its approach to transaction logging. WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, when log records describing the changes have been flushed to permanent storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages can be redone from the log records. (This is roll-forward recovery, also known as REDO.)

PostgreSQL maintains its (WAL) in the pg_xlog subdirectory of the cluster's data directory.

WAL was introduced into PostgreSQL in version 7.1. To maintain database consistency in case of a failure, previous releases forced all data modifications to disk before each transaction commit. With WAL, only one log file must be flushed to disk, greatly improving performance while adding capabilities like Point-In-Time Recovery and transaction archiving.

A PostgreSQL system theoretically produces an indefinitely long sequence of WAL records. The system physically divides this sequence into WAL segment files, which are normally 16MB apiece. The system normally creates a few segment files and then "recycles" them by renaming no-longer-needed segment files to higher segment numbers. If you were to perform a listing of the pg_xlog directory there would always be a handful of files changing names over time.

To add archiving of the WAL files there exists a parameter within the parameter file where a command is added to execute the archival process. Once this is done, Operation System "on-line" backups even become available by executing the pg_start_backup and the pg_stop_backup commands, which suspend and resume writing to the datafiles while continuing to write the transactions to the WAL files and executing the archival process.

Inclusion of WAL archiving and the on-line backup commands were added in version 8.0.

Rollback or Undo

It is interesting how the dynamic allocation of disk space is used for the storage and processing of records within tables. The files that represent the table grow as the table grows. It also grows with transactions that are performed against it. In Oracle there is a concept of rollback or undo segments that hold the information for rolling back a transaction. In PostgreSQL the data is stored within the file that represents the table. So when deletes and updates are performed on a table, the file that represents the object will contain the previous data. This space gets reused but to force recovery of used space, a maintenance process called vacuum must be executed.

Server Log File

Oracle has the alert log file. PostgreSQL has the server log file. A configuration option would even have the connection information we normally see within the Oracle's listener.log appear in PostgreSQL's server log. The parameters within the server configuration file (postgresql.conf) determine the level, location, and name of the log file.

To help with the maintenance of the server log file (it grows rapidly), there exists functionality for rotating the server log file. Parameters can be set to determine when to rotate the file based on the size or age of the file. Management of the old files is then left to the administrator.

Applications

The command initdb creates a new PostgreSQL database cluster.

The command psql starts the terminal-based front-end to PostgreSQL or SQL command prompt. Queries and commands can be executed interactively or through files. The psql command prompt has several attractive features:

• Thorough on-line help for both the psql commands and the SQL syntax.
• Command history and line editing.
• SQL commands could exist on multiple lines and are executed only after the semi-colon (;).
• Several SQL commands separated by semi-colons could be entered on a single line.
• Flexible output formatting.
• Multiple object description commands that are superior to Oracle's DESCRIBE.

Depending on the security configurations of the environments, connections can be established locally or remotely through TCP/IP. Due to these separate security connections passwords may or may not be required to connect.

The command pg_ctl is a utility for displaying status, starting, stopping, or restarting the PostgreSQL database server (postgres). Although the server can be started through the postgres executable, pg_ctl encapsulates tasks such as redirecting log output, properly detaching from the terminal and process group, and providing options for controlled shutdown.

The commands pg_dump and pg_restore are utilities designed for exporting and importing the contents of a PostgreSQL database. Dumps can be output in either script or archive file formats. The script file format creates plain-text files containing the SQL commands required to reconstruct the database to the state it was at the time it was generated. The archive file format creates a file to be used with pg_restore to rebuild the database.

The archive file formats are designed to be portable across architectures. Historically, any type of upgrade to the PostgreSQL software would require a pg_dump of the database prior to the upgrade. Then a pg_restore after the upgrade. Now, for minor releases (i.e., the third decimal – 8.2.x) upgrades can be done in place. However, changing versions at the first or second decimal still requires a pg_dump/pg_restore.

There exists a graphical tool called pgAdmin III developed separately. It is distributed with the Linux and Windows versions of PostgreSQL. Connection to a database server can be established remotely to perform administrative duties. Because the tool is designed to manage all aspects of the database environment, connection to the database must be through a super user account.

The pgAdmin III tool has the following standard attractive features:

• Intuitive layout
• Tree structure for creating and modifying database objects
• Reviewing and saving of SQL when altering or creating objects