Monday, June 26

Up to date access to postgres logs

Andrew's PlanetPostgreSQL
Some of my Italian colleagues have made a nifty little gadget called redislog for pushing postgres logs into Redis, the distributed in-memory cache. From there it can be fed into things like logstash. I thought it would be interesting instead to make the logs available via the Redis Foreign Data Wrapper as a Postgres table. That way we would have easy access to the running logs from Postgres with almost no effort. Here's what I did.First I built and installed redislog and redis_fdw. Then I added redislog to my server's shared_preload_libraries,  set log_min_duration_statement to 0 and restarted.Then I created a database called logger and did this in it:create extension redis_fdw; create server localredis foreign data wrapper redis_fdw; create foreign table redis_postgres_l

Logical Replication in PostgreSQL 10

Petr's PlanetPostgreSQL
PostgreSQL 10 is getting close to its first beta release and it will include the initial support for logical replication, which is was written primarily by me and committed by my colleague Peter Eisentraut, and is internally based on the work 2ndQuadrant did on pglogical (even though the user interface is somewhat different).I'd like to share some overview of basics in this blog post. What's logical replication? Let me start with briefly mentioning what logical replication is and what's it good for. I expect that most people know the PostgreSQL streaming master-standby replication that has been part of PostgreSQL for years and is commonly used both for high availability and read scaling.So why add another replication mechanism and why call it logical? Well, the traditional replic...

Out of tree builds

Andrew's PlanetPostgreSQL
Recently I was asked "what's a vpath build?" If you regularly build postgres from source it's something you should know about. A vpath build is one where the build tree is kept completely separate from the source tree, so you can completely remove the build tree and your source tree is still there, clean and pristine. It's very easy to set up unless you're building with the Microsoft toolset. Essentially what you do is create the root of your build tree, change directory into that directory, and then call configure in your source tree from there.  configure knows all about setting up the vpath tree and does all the work for you. After that, you just run make etc just like you would normally. So it looks like this:mkdir mybuild cd mybuild /path/to/postgresql-source/configure make

In the defense of sar (and how to configure it)

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
Let me discuss a topic that is not inherently PostgreSQL specific, but that I regularly run into while investigating issues on customer systems, evaluating "supportability" of those systems, etc. It's the importance of having a monitoring solution for system metrics, configuring it reasonably, and why sar is still by far my favorite tool (at least on Linux).(more…)

PostgreSQL 10 identity columns explained

Eisentraut's PlanetPostgreSQL, PostgreSQL
For PostgreSQL 10, I have worked on a feature called "identity columns". Depesz already wrote a blog post about it and showed that it works pretty much like serial columns:CREATE TABLE test_old ( id serial PRIMARY KEY, payload text );INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *;andCREATE TABLE test_new ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, payload text );INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING *;do pretty much the same thing, except that the new way is more verbose. ;-)So why bother?CompatibilityThe new syntax conforms to the SQL standard. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. Some...

“Now is Better Than Never” – Why I decided to attend PyCon8…

2ndQuadrant, Britt's PlanetPostgreSQL
…and why I’m glad I did.It’s not all technical… Who knew?!Last year, Pycon7 was held right about the time I joined 2ndQuadrant. Seeing as I was new to the technology AND the Italian language (note: there was an English track), I opted out of attending. Well, after attending Pycon8, I can say that I won’t make that mistake again!Over the past year working in the Open Source community, I’ve learned more technical information than I could have ever imagined. Even then, attending a technical conference and understanding (completely) technical talks seemed a little far-fetched. Or so I thought!Since being introduced to the wonderful world of Open Source, PostgreSQL, and numerous other technologies - I’m continuously fascinated by the way that the communities intertwine. These te

BuildFarm client release 4.19

Andrew's PlanetPostgreSQL
I have released version 4.19 of the PostgreSQL Buildfarm client. It can be downloaded from https://buildfarm.postgresql.org/downloads/releases/build-farm-4_19.tgz Apart from some minor bug fixes, the following changes are made:Include the script's path in @INC. That means you can usually run the script from anywhere rather than just its own directory. Set TZ after "make check" is run. This makes for slightly faster initdb runs in later stages. make TAP tests run with --timer change default log_line_prefix in config file to use %p instead of %c. That makes it easier to tie log messages to other messages that mention a pid Add a module to log running commands to a file as it runs and replace critical uses of `` with the new procedure. That means we have better traces if th...

When autovacuum does not vacuum

2ndQuadrant, PostgreSQL, Tomas' PlanetPostgreSQL
A few weeks ago I explained basics of autovacuum tuning. At the end of that post I promised to look into problems with vacuuming soon. Well, it took a bit longer than I planned, but here we go.To quickly recap, autovacuum is a background process cleaning up dead rows, e.g. old deleted row versions. You can also perform the cleanup manually by running VACUUM, but autovacuum does that automatically depending on the amount of dead rows in the table, at the right moment - not too often but frequently enough to keep the amount of "garbage" under control.(more…)

Using the PostgreSQL TAP framework in extensions

Craig's PlanetPostgreSQL
Support for using the TAP protocol to run extended regression tests was added to PostgreSQL back in 9.4 with the adoption of Perl's prove tool and Test::More to test initdb, pg_basebackup, etc.Since then the TAP-based tests have been greatly expanded, particularly with the advent of the src/test/recovery tests and the PostgresNode module in PostgreSQL 9.6. PostgreSQL now comes with a built-in test harness for easily starting up postgres instances, creating and restoring backups for replication, setting up streaming, and lots more.You can now use this to test your extensions. pg_regress and its limitations Extensions have long supported pg_regress based tests. Just drop the test scripts in sql/. Put the expected results in expected/. List the test names (sans directory and file ex...