Article Preview

COLUMN

More pg_dump

Understanding exactly how your backup command works is critical

Issue: 15.5 (September/October 2017)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a data architect for a major fashion retail brand. But in his 20 years of IT experience, he has been everything from a PC Technician, iSeries System Administrator, iSeries Programmer, Sr. Technical Lead, Data Modeler, Data Architect, Oracle DBA, BI Consultant and Solution Architect. He lives in the great state of Texas with his wife and two kids.
Article Description: No description available.
Article Length (in bytes): 12,982
Starting Page Number: 73
Article Number: 15508
Related Link(s): None

Excerpt of article text...

Hello All! In this column we are going to wrap up our discussion of pg_dump. We are not covering all the options offered by pg_dump and all various scenarios under which you could use them. Instead, the goal is to hit the high points and, hopefully, help you wrap your head around how to implement pg_dump in your own environment.

In the previous column, I had mentioned that you could use pg_dump to backup the database in parallel streams or jobs. So, for large databases, you could very quickly cut the time it takes to backup the entire database. There are a couple of caveats to this, however. First and foremost, you have to be conscious of the fact that each job will count as a connection to your database. By default, Postgres sets the value for max_connections at 100. You can check your setting either from the psql prompt or from a SQL statement. From the psql prompt, type show max_connections; or from a query tool type:

SELECT * FROM pg_settings where name = 'max_connections';

Unless you have changed this setting, you will see it set to 100. This setting is set at run time and is stored in the PostgreSQL configuration file which is called postgresql.conf. The exact location will vary based on the platform and even the distribution of your OS (if you are using Linux). Once you change this value, you must restart in the PostgreSQL instance.

...End of Excerpt. Please purchase the magazine to read the full article.