Tuesday, April 3, 2018

New Annotated Config Files for PostgreSQL 10

Teal Deer: The Annotated.conf has been updated for PostgreSQL 10, and it's a Github repo now.

13 years ago, for PostgreSQL 8.0, I released the first Annotated.conf because we had (at that time) over 140 configuration settings and most people had no idea how to set them.  Today Postgres has nearly twice as many (269), and the problem is twice as bad. More about that later.

The repository is intended to be a quick reference for how to figure out how to set a lot of these parameters.  It includes my tutorial slides in both Libreoffice and PDF format, and a giant table of settings and recommendations in either CSV format or as a PostgreSQL 10 database dump.  Accompanying each setting are my notes on why you'd change the setting and, if so, to what.

Probably more usefully, I've included two sample .conf files.  postgresql.10.simple.conf contains the 20 most commonly changed settings with detailed advice on how to set them.  extra.10.conf has the next 20 most-likely-to-be-changed settings, with somewhat more limited advice.  The idea is for you follow the instructions, and then drop one or both of these files into your include_dir configuration directory and have a more sensible configuration without messing with the preposterously long default configuration file that ships with most PostgreSQL packages.  Those 20 to 40 settings should cover 90% of the needs of 90% of users.

If you feel that my advice is inaccurate, incomplete, or out-of-date, then Github has both Issues and Pull Requests available for you to suggest replacements.

Now, I can't help but feel that the configuration situation with PostgreSQL is getting worse.  While the database has improved with some better sensible defaults, and some good auto-configuration code (for example, for transaction log size), overall both the number of settings and the number of settings most users have to care about keeps going up.

The worst part of this is settings added with absolutely no information on how to determine a reasonable level for the setting.  For example, these four data flushing settings were added in version 10:


... but there is no concrete advice on how to set these. Yes, there are docs, but even as someone who has a solid grounding in database, memory, and filesystem performance, I'd hesitate to recommend any specific level for these for a specific problem.  I'd like to be optimistic and assume that an explanation is coming in Postgres 11 or so, but after four years there's still nothing on how to set vacuum_multixact_freeze_min_age.

In the meantime, hopefully annotated.conf gives you a good start on tinkering with your own PostgreSQL settings.

Monday, January 29, 2018

LWKD Has a New Home!

Since this isn't a particularly good place for it, I've moved Last Week in Kubernetes Development to its new home at lwkd.info.  That includes this week's edition of LWKD.

Part of the idea of the move is to be able to accept contributions to the publication.  Since it's on Github pages, I can accept them through the LWKD git repo.  I particularly could use some help setting up an RSS feed in some way that doesn't require restructuring the site around a static site generator.

Also, a guest writer for next week would be welcome; otherwise the next issue is likely to be light due to personal travel.

Monday, January 22, 2018

Last Week in Kubernetes Development: Week ending January 21

Community Meeting Summary

The demo for this week's meeting was Kubernetes running on Docker For Mac. The Docker.com folks have been hard at work enabling this, and the demo now looks pretty polished. Developers with Mac desktops should be able to easily use Kubernetes with their existing Docker for Mac workflows.

Jaice DuMars explained the delay of the alpha release and gave a 1.10 stats update (see below). Dan Williams updated folks on SIG-Network, who have been making a lot of changes, including adding IPv6 support. Phillip Wittrock updated everyone on what the Steering Committee is currently working on, especially creating template SIG charters so that all SIGs can create their own charters. If you have opinions about the organization and leadership of your SIG, please take the survey on SIG governance.

Kubernetes will be participating in Google Summer of Code with the CNCF this year. Please contact Ihor Dvoretskyi if you are interested in mentoring or know a student. SIG Intros and Deep Dives at KubeCon Europe will be announced soon. The project will have another "Meet Our Contributors" on February 7th, this one focused on helping out new contributors (contact Paris Pittman to participate).

The format for the Community meeting will also be changing slightly in the future. SIGs will be scheduled for updates per release cycle instead of ad hoc, and demo speakers will be asked to rehearse before the meeting.

Release Schedule

This was week 3 of version 1.10 development. This week should have included an early alpha release, mainly as a dry run for release packaging. However, it's been delayed because Branch Manager Caleb Miles had a painful bike accident and has been offline. An alpha release is expected this week.

Feature Freeze, which was supposed to be January 22nd, has also been delayed by one week because the Features Lead is still waiting for status clarification on some features from several SIGs. Final Feature Freeze deadline will now be on the 29th. Many SIGs have updated their features, though, and Ihor has created the Feature Tracking Board for version 1.10.

Feature Work

While 148 patches were merged last week, most of them were minor bug fixes (including at least ten for GCE support), cherry-picks for copying fixes across releases, typo fixes, and some doc and release note corrections. Among the interesting feature work was:




Version Updates


Other Merges


Graph of the Week


This week's graph, brought to you by Jorge Castro, is Approvers and the Approvers Histogram This graph shows you the number of pull request approvals in each repository, and the accompanying histogram shows you who did those approvals. Looks like @cblecker is our leading approver for last month.

Monday, January 15, 2018

Last Week in Kubernetes: Week ending January 14th

With several hundred active contributors, it's pretty hard to keep track of Kubernetes development.   It's hard for me, and I'm paid to keep track; I can't imagine that anyone else can do it, even if they contribute to Kubernetes.

What follows is an experimental publication.  I'm thinking of doing a development summary, every week or so, of what's happened in new features, deprecations, the community meeting, and more.  Tell me if this is useful to you.  If it is, I'll look at finding an official place to publish it where maybe other community members can contribute.

Last Week in Kubernetes: Week ending January 14, 2017

Community Meeting Summary

The community meeting was dominated by a discussion around whether all repos in the kubernetes namespace should be a part of the same automation, particularly merge automation.  Aaron Crickenberger (spiffxp) has been offering this to other repos in the Kubernetes namespace, but some teams, particularly Helm, are concerned about unexpected changes this might cause.  One goal of getting all repos on the same automation is to retire mungegithub.

Jacob Pavlik demonstrated the KQueen cluster manager.  Jaice DuMars went over release 1.10, which is in week 2 of 12 and will go into Feature Freeze on January 22nd, so get your features in!  And SIG Azure and SIG Node made reports.

Feature Work

Configurable Pod Process Namespace Sharing prepared for inclusion in 1.10 this week with the addition of a feature flag for PID namespace sharing. The --docker-disable-shared-pid was also removed from kubelet.

Kubelets can now be run in containers, allowing for a completely containerized Kubernetes install. Such installs are now passing e2e tests.

Support for raw block devices as persistent volumes moved ahead with the merge of iSCSI support for block volumes.


Docker 1.10 is no longer supported. The minimum docker version is now 1.11. While docker 1.10 was officially deprecated in release 1.9, the compatibility code has now actually been completely removed.

sig-cluster-lifecycle is gradually deprecating the /cluster directory in favor of having these cluster setup tools maintained outside of kubernetes/kubernetes. In 1.10, that will include removing the windows/, photon-controller/, libvirt-coreos/, and gce/container-linux/ subdirectories, with more to be removed in future releases.

Version Updates


Other Merges

Wednesday, January 11, 2017

Retiring from the Core Team

Those of you in the PostgreSQL community will have noticed that I haven't been very active for the past year.  My new work on Linux containers and Kubernetes has been even more absorbing than I anticipated, and I just haven't had a lot of time for PostgreSQL work.

For that reason, as of today, I am stepping down from the PostgreSQL Core Team.

I joined the PostgreSQL Core Team in 2003.  I decided to take on project advocacy, with the goal of making PostgreSQL one of the top three databases in the world.  Thanks to the many contributions by both advocacy volunteers and developers -- as well as the efforts by companies like EnterpriseDB and Heroku -- we've achieved that goal.  Along the way, we proved that community ownership of an OSS project can compete with, and ultimately outlast, venture-funded startups.

Now we need new leadership who can take PostgreSQL to the next phase of world domination.  So I am joining Vadim, Jan, Thomas, and Marc in clearing the way for others.

I'll still be around and still contributing to PostgreSQL in various ways, mostly around running the database in container clouds.  It'll take a while for me to hand off all of my PR responsibilities for the project (assuming that I ever hand all of them off).

It's been a long, fun ride, and I'm proud of the PostgreSQL we have today: both the database, and the community.  Thank you for sharing it with me.

Wednesday, May 18, 2016

Changing PostgreSQL Version Numbering

Per yesterday's developer meeting, the PostgreSQL Project is contemplating a change to how we do version numbers.  First, let me explain how we do version numbers now.  Our current version number composition is:

9 . 5 . 3 
Major1 . Major2 . Minor

That is, the second number is the "major version" number, reflecting our annual release.  The third number is the update release number, reflecting cumulative patch releases.  Therefore "9.5.3" is the third update to to version 9.5.

The problem is the first number, in that we have no clear criteria when to advance it.  Historically, we've advanced it because of major milestones in feature development: crash-proofing for 7.0, Windows port for 8.0, and in-core replication for 9.0.  However, as PostgreSQL's feature set matures, it has become less and less clear on what milestones would be considered "first digit" releases.  The result is arguments about version numbering on the mailing lists every year which waste time and irritate developers.

As a result, the PostgreSQL Project is proposing a version numbering change, to the following:

10 . 2
Major . Minor

Thus "10.2" would be the second update release for major version 10.   The version we release in 2017 would be "10" (instead of 10.0), and the version we release in 2018 will be "11".

The "sortable" version number available from the server, libpq, and elsewhere would remain the same six digits, zero-filled in the middle.  So 10.2 would be 100002.

The idea is that this will both put an end to the annual arguments, as well as ending the need to explain to users that 9.5 to 9.6 is really a major version upgrade requiring downtime.

Obviously, there is potential for breakage of a lot of tools, scripts, automation, packaging and more in this.  That's one reason we're discussing this now, almost a year before 10 beta is due to come out.

The reason for this blog post is that I'm looking for feedback on what this version number change will break for you.  Particularly, I want to hear from driver authors, automation engineers, cloud owners, application stack owners, and other folks who are "downstream" of PostgreSQL.  Please let us know what technical problems this will cause for you, and how difficult it will be to resolve them in the next nine months.

We are not, at this point, interested in comments on how you feel about the version change or alternate version naming schemes.  That discussion has already happened, at length.  You can read it here, here, and here, as well as at the developer meeting.

Places to provide feedback:

Thanks for any feedback you can provide.

Note that the next release of PostgreSQL, due later this year, will be "9.6" regardless.  We're deciding what we do after that.

Thursday, April 28, 2016

Don't delete pg_xlog

This StackOverflow question reminded me of this old blog post, which is still relevant today:

pg_log, pg_xlog and pg_clog

There are three directories in a default $PGDATA directory when you create it which are named "pg_*log".


$PGDATA/pg_log is the default location for the database activity logs, which include error messages, query logging, and startup/shutdown messages.  This is where you should first look for information when PostgreSQL won't start.  Many Linux distributions and other packaging systems relocate this log directory to somewhere like /var/log/postgresql.

You can freely delete, rename, compress, and move files in pg_log without penalty, as long as the postgres user still has rights to write to the directory. If pg_log becomes bloated with many large files, you probably need to decrease the number of things you're logging by changing the settings in postgresql.conf.

Do note that if you "delete" the current log file on a Linux or Unix system, it may remain open but not accessible, just sending any successive log messages to /dev/null until the file rotates.


$PGDATA/pg_xlog is the PostgreSQL transaction log.  This set of binary log files, with names like '00000001000000000000008E', contain images of the data from recent transactions.  These logs are also used for binary replication.

If replication, archiving, or PITR is failing, this directory can become bloated with gigabytes of logs the database server is saving for when archiving resumes. This can cause you to run out of disk space
Unlike pg_log, you may not freely delete, move, or compress files in this directory.  You may not even move the directory without symlinking it back to its original location.  Deleting pg_xlog files may result in unrecoverable database corruption.

If you find yourself in a situation where you've got 100GB of files in pg_xlog and the database won't start, and you've already disabled archiving/replication and tried clearing disk space every other way, then please take two steps:
  1. Move files from pg_xlog to a backup disk or shared network drive, don't delete them, and
  2. Move only a few of the oldest files, enough to allow PostgreSQL to start again.


$PGDATA/pg_clog contains a log of transaction metadata.   This log tells PostgreSQL which transactions completed and which did not.  The clog is small and never has any reason to become bloated, so you should never have any reason to touch it.

Should you ever delete files from pg_clog, you might as well delete the entire database directory. There is no recovery from a missing clog.

Note that this means, if you back up the files in a $PGDATA directory, you should make sure to include the pg_clog and pg_xlog as well, or you may find that your backup is not usable.