Learning PostgreSQL

Learning PostgreSQL

Overview of this book

PostgreSQL is one of the most powerful and easy to use database management systems. It supports the most advanced features included in SQL standards. The book starts with the introduction of relational databases with PostegreSQL. It then moves on to covering data definition language (DDL) with emphasis on PostgreSQL and common DDL commands supported by ANSI SQL. You will then learn the data manipulation language (DML), and advanced topics like locking and multi version concurrency control (MVCC). This will give you a very robust background to tune and troubleshoot your application. The book then covers the implementation of data models in the database such as creating tables, setting up integrity constraints, building indexes, defining views and other schema objects. Next, it will give you an overview about the NoSQL capabilities of PostgreSQL along with Hstore, XML, Json and arrays. Finally by the end of the book, you'll learn to use the JDBC driver and manipulate data objects in the Hibernate framework.

Learning PostgreSQL

Credits

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Free Chapter

Relational Databases

Database management systems

Relational algebra

Data modeling

Summary

PostgreSQL in Action

An overview of PostgreSQL

PostgreSQL capabilities

Installing PostgreSQL

Summary

PostgreSQL Basic Building Blocks

Database coding

PostgreSQL objects hierarchy

PostgreSQL high-level object interaction

PostgreSQL database components

Summary

PostgreSQL Advanced Building Blocks

Views

Indexes

Functions

PostgreSQL user-defined data types

Triggers and rule systems

Summary

SQL Language

SQL fundamentals

Querying the data with the SELECT statement

Changing the data in the database

Summary

Advanced Query Writing

Common table expressions

Window functions

Advanced SQL

Transaction isolation and multiversion concurrency control

Summary

Server-Side Programming with PL/pgSQL

Introduction

PostgreSQL function parameters

The PostgreSQL PL/pgSQL control statements

Exception handling

Dynamic SQL

Summary

PostgreSQL Security

Authentication in PostgreSQL

PostgreSQL default access privileges

PostgreSQL security levels

Encrypting data

Summary

The PostgreSQL System Catalog and System Administration Functions

The system catalog

Getting the database cluster and client tools version

Terminating and canceling user sessions

Setting and getting database cluster settings

Getting the database and database object size

Cleaning up the database

Cleaning up data in the database

Managing database locks

Adding missing indexes on foreign keys and altering the default statistic

Getting the views dependency tree

Summary

Optimizing Database Performance

PostgreSQL configuration tuning

Tuning PostgreSQL queries

Cross column correlation

Table partitioning

Summary

Beyond Conventional Data types

PostgreSQL arrays

Hash store

The PostgreSQL JSON data type

A PostgreSQL full text search

Summary

Testing

Unit testing

Schema difference

The interfaces test

PostgreSQL benchmarks

Summary

PostgreSQL JDBC

Introduction to JDBC

Connecting to a PostgreSQL database

Issuing a query and processing the results

Function handling

Design considerations

Summary

PostgreSQL and Hibernate

Introduction to ORM and Hibernate

Installation and configuration

Working with entities

Summary

Index

Customer Reviews

5 star

4 star

3 star

2 star

1 star

Authentication in PostgreSQL

Authentication answers the question, who is the user? PostgreSQL supports several authentication methods, including trust, ident, password, GSSAPI, SSPI, LDAP, PAM, and so on. To understand authentication, one needs to have the following information:

Authentication is controlled via a pg_hba.conf file, where hba stands for host-based authentication.
It is good to know the default initial authentication settings shipped with PostgreSQL distribution.
The pg_hba.conf file is often located in the data directory, but it also can be specified in the postgresql.conf configuration file.
When changing the authentication, one needs to send a SIGHUP signal, and this is done via several methods based on the PostgreSQL platform. Also note that the user who sends the signal should be a superuser or the postgres or a root system user on the Linux distribution; again, this depends on the platform.
```
psql -U postgres -c "SELECT pg_reload_conf();"
sudo service postgresql reload
sudo...
```

Learning PostgreSQL

Learning PostgreSQL

Overview of this book

Related Content you might be interested in

Current Title:

Learning PostgreSQL

Authentication in PostgreSQL