Archive for the ‘postgresql’ Category

Training is a core part of what EnterpriseDB provides to the user community. And as that community grows and evolves, we always try to revise and revamp our courses to fit users’ needs.

Last fall, we rebuilt our training program from the ground up in order to make our classes more dynamic and interactive. We also developed two new, separate courses: Introduction to PostgreSQL Administration and Advanced PostgreSQL Administration.

We have now revamped our training program even further, combining these two courses into a single, five-day live virtual training course, Comprehensive PostgreSQL Administration. The course will be taught by Bruce Momjian, a Senior Database Architect at EDB as well as a PostgreSQL community leader and Global Development Group co-founder. This new course will draw upon both the Introductory and Advanced courses to give a comprehensive understanding of core administrative tasks including configuration, maintenance, monitoring, backups, recovery, and moving data. The advanced topics are also covered, including: replication to safeguard data, clustering for high availability, partitioning for performance and maintainability, and security for privacy and protection.

“Knowing how to work with PostgreSQL has become recognized as a valuable career path for individuals,” Bruce says. “I see experienced enterprise software professionals who are versed in operating systems and Windows, for example, taking the training to advance their careers. The skills are in high demand and people now see they have a range of options once they’ve (more…)

If you're new here, you may want to subscribe to our RSS feed or follow us on Twitter for product announcements, event information, and industry news.

PostgreSQL 9.3 has two key software updates making switchover/switchback easier in High Availability configurations.

First, let’s address the software patches and their descriptions:
1. First patch was committed by Fujii Masao.
Patch commit# 985bd7d49726c9f178558491d31a570d47340459

With this patch, the walsender process tries to send all outstanding WAL records to the standby in replication when the user shuts down the master.

This means:
a. All WAL records are synced between two servers after the clean shutdown of the master
b. After promoting the standby to new master, the user can restart the stopped master as new standby without a fresh backup from new master.

Before PostgreSQL version 9.3, streaming replication used to stop replicating if the timeline on the primary didn’t match the standby. This generally happens when the user promotes one of the standbys to become the new master. Promoting a standby always results in an increment of timeline ID and after that increment, other standbys will refuse to continue replicating.

With this patch in PostgreSQL 9.3, the standby asks the primary for any timeline history files that are missing from the standby when it connects – if the standby recovery.conf file has the following setting:(more…)

PostgreSQL 9.3 has two key software updates making switchover/switchback easier in High Availability configurations.

First, let’s address the software patches and their descriptions:
1. First patch was committed by Fujii Masao.
Patch commit# 985bd7d49726c9f178558491d31a570d47340459

With this patch, the walsender process tries to send all outstanding WAL records to the standby in replication when the user shuts down the master.

This means:
a. All WAL records are synced between two servers after the clean shutdown of the master
b. After promoting the standby to new master, the user can restart the stopped master as new standby without a fresh backup from new master.

Before PostgreSQL version 9.3, streaming replication used to stop replicating if the timeline on the primary didn’t match the standby. This generally happens when the user promotes one of the standbys to become the new master. Promoting a standby always results in an increment of timeline ID and after that increment, other standbys will refuse to continue replicating.

With this patch in PostgreSQL 9.3, the standby asks the primary for any timeline history files that are missing from the standby when it connects – if the standby recovery.conf file has the following setting:
recovery_target_timeline=’latest’

The missing files are sent using a new replication command TIMELINE_HISTORY, and stored in the standby’s pg_xlog directory. Using the timeline history files, the standby can follow the latest timeline present in the primary, just as it can follow new timelines appearing in an archive WAL directory.

Because of above patches, if the user performs the following sequence of steps then switchover/switchback can be easily achieved:
To switchover from Master to Standby, use following steps:
On Master:
1. Use any one of following stop options for clean shutdown.

PostgreSQL has advanced significantly in features and capabilities in recent releases, prompting companies worldwide to accelerate their adoption of PostgreSQL. With this rising interest come more and more queries from users of Oracle, DB2 and SQL Server exploring PostgreSQL (commonly called Postgres) for their individual needs. With this rising interest in Postgres, which threatens the status quo of traditional database vendors, is an associated increase in FUD (Fear, Uncertainty and Doubt) about Postgres.

One population of user–SQL Server users–has become significantly active in investigating Postgres. This is possibly because of recent changes in Microsoft pricing policies for SQL Server, with users concluding they are simply paying too much for their database. But this population has clearly been served a healthy dose of FUD.

Postgres ignorance is understandable if one has spent much of their career in the Microsoft fold but the misinformation is more than likely the result of FUD-driven marketing. But if some of the assertions they’re being told were true, that using Postgres would put their operations at risk, then major corporations like ABN AMRO Bank, Grupo BBVA, Deutsche Börse AG, Ericsson, Fujitsu, KT Corp., Lockheed Martin, McKesson, Corp., Nippon Telegraph & Telephone Corp. (NTT) and RSA Security LLC, as well as agencies throughout the U.S. government, including many within the Department of Defense and NSA, would not be using Postgres.

EnterpriseDB has prepared a position paper to clarify many misconceptions about Postgres among SQL Server users and to explain what the open source PostgreSQL project really is and how it functions. In many ways, it operates with more discipline, vigilance, visibility and predictability than many major software vendors we know, and they deliver code with far fewer bugs. We have some examples to illustrate that.

It’s called the brain of the database. The optimizer in PostgreSQL interprets SQL queries and determines the fastest method of execution. That’s according to Bruce Momjian, EnterpriseDB senior database architect and co-founder of the PostgreSQL Global Development Group. Bruce delved into the PostgreSQL optimizer during the recent PG Con 2014.

As Bruce described his lecture, he tapped the EXPLAIN command to show how the optimizer interprets queries and determines optimal execution. He covered examples of scan methods, index selection, join types, and how ANALYZE statistics influence their selection.

The goal was to help developers and DBAs understand how queries in PostgreSQL are executed and how to optimize that process.

One of the development goals for PostgreSQL is expanding parallelism on the server side. Postgres already supports full parallelism in coding on the client side. Developers can write application to open multiple database connections and manage them asynchronously.

Robert Haas, EnterpriseDB chief architect and PostgreSQL major contributor, has been championing the development efforts to expand parallelism to enable Postgres to utilize multiple CPUs and I/O channels.

During the recent PG Con 2014 conference in Ottawa, Robert delivered a presentation on the state of parallelism development today and where it’s headed. View the Powerpoint or watch the video of his talk.

For more information on the advanced features that EDB has developed for Postgres, contact us.

For those who haven't read previous discussion on this topic, either on my blog, on pgsql-performance, or elsewhere around the Internet, enabling vm.zone_reclaim_mode can cause a lot of problems for applications, such as PostgreSQL, that make use of more page cache than will fit on a single NUMA node. Pages may get evicted from memory in preference to using memory on other nodes, effectively resulting in a page cache that is much smaller than available free memory. See the second of the two blog posts linked above for more details.

PostgreSQL isn't the only application that suffers from non-zero values of this setting, so I think a lot of people will be happy to see this change merged (like the guy who said that this setting is the essence of all evil). It will doubtless take some time for this to make its way into mainstream Linux distributions, but getting the upstream change made is the first step. Thanks to Mel Gorman for pursuing this.

The PostgreSQL community recently unveiled its next release with a PostgreSQL 9.4 beta candidate for public review and comment. This much-anticipated release kept up the momentum around JSON and features JSONB, a new JSON data type that stores JSON documents in a binary format that doesn’t need to be re-parsed when accessed, allowing for much faster querying and indexing.

PostgreSQL 9.4 beta was, understandably, a major topic of discussion during the PG Conf NYC 2014 PostgreSQL conference in New York last month. EnterpriseDB’s very own chief architect of tools and installers Dave Page, who wears several hats in the PostgreSQL community including core team member and secretary of PostgreSQL Europe, delivered a comprehensive overview of what’s coming in PostgreSQL 9.4 beta.

The people using PostgreSQL and the Streaming Replication feature seem to ask many of the same questions:

1. How best to monitor Streaming Replication?

2. What is the best way to do that?

3. Are there alternatives, when monitoring on Standby, to using the pg_stat_replication view on Master?

4. How should I calculate replication lag-time, in seconds, minutes, etc.?

In light of these commonly asked questions, I thought a blog would help. The following are some methods I’ve found to be useful.

Monitoring is critical for large infrastructure deployments where you have Streaming Replication for:

1. Disaster recovery

2. Streaming Replication is for High Availability

3. Load balancing, when using Streaming Replication with Hot Standby

PostgreSQL has some building blocks for replication monitoring, and the following are some important functions and views which can be use for monitoring the replication:

1. pg_stat_replication view on master/primary server.

This view helps in monitoring the standby on Master. It gives you the following details:

pid: Process id of walsender process
usesysid: OID of user which is used for Streaming replication.
usename: Name of user which is used for Streaming replication
application_name: Application name connected to master
client_addr: Address of standby/streaming replication
client_hostname: Hostname of standby.
client_port: TCP port number on which standby communicating with WAL sender
backend_start: Start time when SR connected to Master.
state: Current WAL sender state i.e streaming
sent_location: Last transaction location sent to standby.
write_location: Last transaction written on disk at standby
flush_location: Last transaction flush on disk at standby.
replay_location: Last transaction flush on disk at standby.
sync_priority: Priority of standby server being chosen as synchronous standby
sync_state: Sync State of standby (is it async or synchronous).