You're reading from PostgreSQL High Performance Cookbook Mastering query optimization, database monitoring, and performance-tuning for PostgreSQL

Product type Paperback

Published in Mar 2017

Publisher Packt

ISBN-13 9781785284335

Length 360 pages

Edition 1st Edition

Languages

SQL

Tools

PostgreSQL

Concepts

High Performance Programming

Authors (2):

Chauhan

Dinesh Kumar

View More author details

Table of Contents (13) Chapters

Preface

1. Database Benchmarking FREE CHAPTER

2. Server Configuration and Control

3. Device Optimization

4. Monitoring Server Performance

5. Connection Pooling and Database Partitioning

6. High Availability and Replication

7. Working with Third-Party Replication Management Utilities

8. Database Monitoring and Performance

9. Vacuum Internals

10. Data Migration from Other Databases to PostgreSQL and Upgrading the PostgreSQL Cluster

11. Query Optimization

12. Database Indexing

Using pg_dump to upgrade data

In this recipe, we are going to upgrade the PostgreSQL cluster from version 9.5 to 9.6. We will utilize the pg_dump utility for this purpose.

Getting ready

The only prerequisites here are that an existing PostgreSQL cluster must be set up and running. The required version here is PostgreSQL Version 9.6. These steps are carried out on a 64 bit CentOS machine.

How to do it...

Here are the series of steps that need to be carried out for upgrading PostgreSQL from version 9.5 to 9.6 using pg_dump:

Back up your database using pg_dumpall:
```
        pg_dumpall >  db.backup
```
The next step would be to shut down the current PostgreSQL server:
```
        pg_ctl -D /var/lib/pgsql/9.5/data  stop
```

Rename the old PostgreSQL installation directory:

        mv /var/lib/pgsql  /var/lib/pgsql.old

Install the new version of PostgreSQL, which is PostgreSQL Version 9.6, by using either the yum command or the PostgreSQL binaries from: https://www.bigsql.org/postgresql/installers.jsp.
The...

The rest of the chapter is locked

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $19.99/month. Cancel anytime

Authors (2)

Chauhan

Chitij Chauhan currently works as a senior database administrator at an IT-based MNC in Chandigarh. He has over 10 years of work experience in the field of database and system administration, with specialization in MySQL clustering, PostgreSQL, Greenplum, Informix DB2, SQL Server 2008, Sybase, and Oracle. He is a leading expert in the area of database security, with expertise in database security products such as IBM InfoSphere Guardium, Oracle Database Vault, and Imperva.

See other products by Chauhan

Dinesh Kumar

Dinesh Kumar is an enthusiastic open source developer and has written several open source tools for PostgreSQL. He recently announced pgBucket, a brand new job scheduler for PostgreSQL. He is also a frequent blogger at manojadinesh.blogpsot.com, where he talks more about PostgreSQL. He is currently working as a senior database engineer in OpenSCG and building the PostgreSQL cloud operations. He has more than 6 years of experience as an Oracle and PostgreSQL database administrator and developer, and is currently focusing on PostgreSQL.

See other products by Dinesh Kumar