cancel
Showing results for 
Search instead for 
Did you mean: 

Getting started with PostgreSQL?

Question

 I'm just getting started with PostgreSQL.  Where do I start?

Answer

 There are many places to start; PostgreSQL.org has a ton of mailing list threads related to this question and probably thousands of others, and Stack Overflow holds a lot of answer-oriented information as well.  Here are a few good places you can start:

 

1. Documentation:

Postgres Documentation

2. Couple of videos about architecture and administration on youtube::

Postgres Internals

3. Free online training::

Free Trainings

3. And tons of blogs from Robert and other contributors.

 

Below is a basic overview that was drafted by Sachin Fate of EDB:

 

Architectural Overview of PostgreSQL

 

PostgreSQL is the world's most advanced open source database. It is built on top of many of the same RDBMS fundamentals which are the basis of all modern-day relational databases, such as Oracle, DB2, SQL Server.

A Postgres database cluster is a collection of databases which is stored at a common file system location, or data directory.

 

 

postgresql.jpg

 

POSTMASTER :

When you start the postgresql database cluster, it will start the postmaster process as well, and will allocate shared memory to the database cluster. The postmaster does not itself interact with the user and should be started as a background process. Only one postmaster should be run on server for one database cluster.

The postmaster process is one of the most important processes in postgresql. It works as a supervisor process, which means that it monitor all the processes, and whenever any process fail, it will try to restart it.

The postmaster is also work as a listener in postgresql; it accepts the connection requests from users, and validates it, then it will initiate a new process for additional new connection requests.

SHARED MEMORY:

A shared memory area is allocated by a postgresql server during start up. This area is also divided into several fixed-size sub-areas.

1) Shared Buffer : PostgreSQL loads pages within tables and indexes from a persistent storage object to the buffer, and operates them directly.

2) WAL buffer : To ensure that no data is lost by server failures, PostgreSQL supports the write-ahead log, or WAL mechanism. WAL data (also referred to as “xlog” records) are transaction logs in PostgreSQL; the WAL buffer is a buffering area which stages the WAL data before writing to the persistent storage.

 

BACKGROUND PROCESSES :

BGWRITER: Also known as the background writer, the role of BGWRITER is to reduce the impact of intensive checkpoint writing. The background writer continues to flush dirty pages incrementally, with the intent of causing a minimal impact on any critical database activity.  By default, the background writer wakes every 200 milliseconds (as defined by bgwriter_delay) and flushes bgwriter_lru_maxpages (the default is 100 pages) at maximum.

CHECKPOINTER : CHECKPOINTER, the checkpoint process, has two aspects: the preparation of database recovery points, and the cleaning of dirty pages on the shared buffer pool.

AUTO VACUUM : Autovacuum is a daemon within PostgreSQL which automates the execution of VACUUM and ANALYZE commands; when enabled, it calls the autovacuum worker processes – or, more precisely, requests a spawning of the processes from the PostgreSQL server.

WAL WRITER : The WAL Writer process periodically writes and flushes records within the WAL data on the WAL buffer to persistent storage.

STATASTIC COLLECTOR : In this process, statistical information, such as the attributes for pg_stat_activity or pg_stat_database, is collected.

ARCHIVER : In this process, archiving logging is executed.

LOG WRITER : This process writes error messages into log files.

Version history
Revision #:
1 of 1
Last update:
a month ago
Updated by:
 
Contributors