Book Image

PostgreSQL for Data Architects

By : Jayadevan M
Book Image

PostgreSQL for Data Architects

By: Jayadevan M

Overview of this book

This book is for developers and data architects who have some exposure to databases. It is assumed that you understand the basic concepts of tables and common database objects, including privileges and security.

Related Content you might be interested in

Current Title:

Small book image
PostgreSQL for Data Architects
Jayadevan M
Mar, 2015 | 272 pages
No titles found
Table of Contents (14 chapters)
Preface
Preface
What this book covers
What you need for this book
Who this book is for
Conventions
Reader feedback
Customer support
Free Chapter
1
1. Installing PostgreSQL
1. Installing PostgreSQL
Installation options
Dependencies to compile the source
Configuring and creating the makefile
Building and creating the executables
Installing and moving the files to where they belong
Initializing a cluster
Working with extensions
Summary
2
2. Server Architecture
2. Server Architecture
Starting with the daemon process
Understanding the shared buffer
Checkpoint
WAL and the WAL writer process
The background writer
The autovacuum launcher process
The logging process
The stats collector process
The WAL sender and WAL receiver
Sorting in memory with work_mem
Maintenance with maintenance_work_mem
Summary
3
3. PostgreSQL – Object Hierarchy and Roles
3. PostgreSQL – Object Hierarchy and Roles
The PostgreSQL cluster
Understanding tablespaces
Databases, schemas, and search_path
Roles and privileges
Summary
4
4. Working with Transactions
4. Working with Transactions
Understanding transactions
PostgreSQL and MVCC
Summary
5
5. Data Modeling with SQL Power Architect
5. Data Modeling with SQL Power Architect
Tools for databases and their uses
Database design tools
Generating SQL
Summary
6
6. Client Tools
6. Client Tools
GUI tools and command-line tools
pgAdmin – downloading and installation
psql – working from the command line
Summary
7
7. SQL Tuning
7. SQL Tuning
Understanding basic facts about databases
Query execution components
Finding the execution plan
Optimization guidelines and catches
Summary
8
8. Server Tuning
8. Server Tuning
Server-wide memory settings
Managing writes, connections, and maintenance
Seek/scan cost and statistics parameters
Materialized views
Partitioned tables
Summary
9
9. Tools to Move Data in and out of PostgreSQL
9. Tools to Move Data in and out of PostgreSQL
Setting up the production database – considerations
COPY
Fast loading with pg_bulkload
pg_dump
Filtering options
Summary
10
10. Scaling, Replication, and Backup and Recovery
10. Scaling, Replication, and Backup and Recovery
Scalability
Point-in-time recovery
Summary
11
11. PostgreSQL – Troubleshooting
11. PostgreSQL – Troubleshooting
Connection issues
Authentication and permission issues
Parameter changes not effective
Query not responding
Summary
12
12. PostgreSQL – Extras
12. PostgreSQL – Extras
Interesting data types
Features over time
Summary
13
Index
Index

Chapter 1. Installing PostgreSQL

This chapter gives you an overview of the process to install PostgreSQL from the source. The system used for installation and providing examples in the following sections is a 64-bit CentOS (6.4) machine. Other Unix/Linux systems typically have similar commands. For those using Windows systems, there is a set of utilities available at http://sourceforge.net/projects/unxutils/, which makes it possible to execute most of the Unix commands (find, grep, cut, and so on) in the Windows environment. The steps to be followed to install PostgreSQL on Windows are very different compared to those for Unix/Linux systems and are not covered in this chapter.

Previous Section
End of Section 1
Next Section