Quick Logical Replication Checklist

By request…

Before starting the publication/subscription, check your schemas.
(Adjust if you’re not replicating all tables, or have dropped indexes/constraints to make the initial sync go faster)
– Do you have the correct number of tables on both sides?
– Do you have the correct number of indexes on both sides?
– Do you have the same number of constraints on both sides?
– Are all of the indexes and constraints valid?

After starting replication, check the logs on the publisher and the subscriber for errors.
– Investigate anything unusual.
– If you’re getting duplicate key errors, check the old database logs first – this may be normal!
– If it’s not normal, this can indicate something is writing to the subscriber. Find it and make it stop¹, then fix the situation².
– If you are getting duplicate key errors after reversing the direction of repication: did you remember to copy the sequences over?

Then connect to the databases and check them out from psql.

On the publisher:

/* "describe publication" - is this information correct? */
\dRp+

/*
pg_replication_slots.slot_name should be the name of the subscription (as it appears on the subscriber)
pg_replication_slots.type should be 'logical'
pg_replication_slots.active should be 't'
*/
SELECT * FROM pg_replication_slots;

/*
pg_stat_replication.application_name should be the name of the subscription (as it appears on the subscriber)
pg_stat_replication.state should be 'streaming'
*/
SELECT * FROM pg_stat_replication;

/* This query should return nothing unexpected: */
SELECT schemaname, relname FROM pg_stat_user_tables
WHERE relname NOT IN (SELECT tablename FROM pg_publication_tables);

On the subscriber:

/* "describe subscription" - is this information correct? */
\dRs+

/* When replication is caught up, pg_subscription_rel.srsubstate will be 'r' for all tables */
SELECT srrelid::regclass, srsubstate, srsublsn
FROM pg_subscription_rel order by srsubstate;
/*
srsubstate key:
i = initialize
d = data is being copied
f = finished table copy
s = synchronized
r = ready (normal replication)
*/

Spot-check data to verify publisher and subscriber match.

If you switch replication direction, run the above checks again.

—
1 – You may want to enable `default_transaction_read_only` mode on the subscriber. Yes, logical replication will still work. If you’re on AWS, make the change to your parameter group, as you can’t use `ALTER SYSTEM`.
2 – Fix your data by either starting over from the initial copy step, or refilling just the affected tables (briefly: drop table from publication, refresh subscription, truncate table on the subscriber, add table back to publication, refresh subscription³). Depending on the extent of the damage, it can be better to just start over.
3 – It’s not a bad idea to rehearse this particular scenario in staging.

Quick connection string/connectivity test using DBLINK

Ever had that sinking feeling when you’re setting up logical replication and CREATE SUBSCRIPTION... just kinda sat there? Maybe it eventually timed out?

I used to use foreign data wrappers to troubleshoot database-to-database connectivity problems, but using DBLINK is much easier. (Bonus: can be used to troubleshoot connectivity problems with foreign data wrappers, also!) Hat tip to Xof for sharing it.

-- Create the DBLINK extension in both databases
CREATE EXTENSION dblink;

-- From either side:
SELECT * FROM dblink (
'',
'SELECT 1'
) AS test(i int);

Example successful output:

gabrielle=# SELECT * FROM dblink (
    'port=5433 dbname=gabrielle',
    'SELECT 1'
  ) AS test(i int);

 i 
---
 1
(1 row)

PgOpen 2016 recap

It’s been a few weeks now since we wrapped up the most recent PostgresOpen in Dallas.

Marianne Bellotti’s keynote was a fantastic and very interesting look into the current state of government IT.  I hope we get to see her at more conferences!

Other quick highlights:
– 2ndQ has a running beta of WordPress on Postgres! (Simon’s keynote)
– Early decisions by the Postgres team (e.g. to spend time working on subselects) directly resulted in my choosing Postgres over MySQL a few years later. (From Greg Stark’s “A Look at the Elephant’s Tail”)
– Keith Fiske (“Managing OS Provided PostgreSQL Packages”) gave a rundown of the vagaries of using Pg packages on different OSes, something that has occasionally driven me around the bend.

I got the most bang for my buck from the combination of Magnus’s Haganders “A Look at the Elephant’s Trunk” and Denish Patel’s “Advanced Postgres Monitoring” (slide deck here). Denish has a thorough list of metrics you should collect, and there are some changes coming in 9.6 to pg_stat_activity that may break current monitoring scripts that use it.

(Also: I am VERY EXCITED about pg_stat_progress_vacuum!)

(And I learned that NumPy is pronounced num-pie and not “rhymes with lumpy”.)

Bonus speaker tip: Many years ago I was in a talk in which the presenter asked “Does everybody understand [x]?” When noone responded, he continued “Just in case we have some people here who are too shy to raise their hands, [x] is…” and gave a brief explanation, enough for newer folks to follow along for the rest of the session. This is an excellent inclusion tactic. Scott Meade demonstrated another way to handle this situation: “So we’re at level set, [x] refers to …”

Thanks to our attendees, speakers, and sponsors for making this conference what it is. The committee is meeting this week to make plans for next year.  Watch the conference website and twitter feed for updates.

Easy clock dimension table

We’re currently redesigning our data warehouse, and we’re experimenting with a clock (or time, or time-of-day) dimension to represent the time of day, separate from the date dimension we already use. This table should contain a record for each minute of a day, allowing us to easily determine business hours in various timezones, etc etc.

I’m not entirely sure it’s going to work for us just yet; we have a lot of timestamp data, and seems like it’s more trouble than it’s worth¹. But it never hurts to proof-of-concept something, right? (Except for all those times the proof-of-concept ends up being the production system…)

Anyway, somebody commented that “you could have the analyst whip up the dataset in just a few hours” … :sideeye: or, I could do it myself in Postgres in just a few minutes:

CREATE TABLE clock_dimension (
 id serial primary key
 , full_timestamp time
 , hour_of_day_24 integer
 , hour_of_day_12 integer
 , am_pm text
 , minute_of_hour integer
 , business_hours_weekday boolean
 , business_hours_weekend boolean
 );

-- fill it with values
INSERT INTO clock_dimension (full_timestamp) VALUES (
generate_series('2016-08-05 00:00'::timestamp, '2016-08-05 23:59'::timestamp, '1 minute')::time
);
UPDATE clock_dimension SET
hour_of_day_24 = date_part('hour', full_timestamp)
, hour_of_day_12 = CASE
WHEN date_part('hour', full_timestamp) > 12
THEN date_part('hour', full_timestamp) - 12
WHEN date_part('hour', full_timestamp) = 0
THEN 12
ELSE date_part('hour', full_timestamp)
END
, am_pm = CASE
WHEN date_part('hour', full_timestamp) >=12
THEN 'pm'
ELSE 'am'
END
, minute_of_hour = date_part('minute', full_timestamp)
, business_hours_weekday = CASE
WHEN full_timestamp BETWEEN '08:00' AND '21:00'
THEN 't'::boolean
ELSE 'f'::boolean
END
, business_hours_weekend = 'f'
;

Sample data:

 id  | full_timestamp | hour_of_day_24 | hour_of_day_12 | am_pm | minute_of_hour | business_hours_weekday | business_hours_weekend 
-----+----------------+----------------+----------------+-------+----------------+------------------------+------------------------
 901 | 15:00:00       |             15 |              3 | pm    |              0 | t                      | f
 902 | 15:01:00       |             15 |              3 | pm    |              1 | t                      | f
 903 | 15:02:00       |             15 |              3 | pm    |              2 | t                      | f
 904 | 15:03:00       |             15 |              3 | pm    |              3 | t                      | f
 905 | 15:04:00       |             15 |              3 | pm    |              4 | t                      | f
 906 | 15:05:00       |             15 |              3 | pm    |              5 | t                      | f
 907 | 15:06:00       |             15 |              3 | pm    |              6 | t                      | f
 908 | 15:07:00       |             15 |              3 | pm    |              7 | t                      | f
 909 | 15:08:00       |             15 |              3 | pm    |              8 | t                      | f
 910 | 15:09:00       |             15 |              3 | pm    |              9 | t                      | f
 911 | 15:10:00       |             15 |              3 | pm    |             10 | t                      | f
 912 | 15:11:00       |             15 |              3 | pm    |             11 | t                      | f
 913 | 15:12:00       |             15 |              3 | pm    |             12 | t                      | f
 914 | 15:13:00       |             15 |              3 | pm    |             13 | t                      | f
 915 | 15:14:00       |             15 |              3 | pm    |             14 | t                      | f
 916 | 15:15:00       |             15 |              3 | pm    |             15 | t                      | f

—

1 – see also http://www.kimballgroup.com/2004/02/design-tip-51-latest-thinking-on-time-dimension-tables/

Slow Query Checklist

Every slow query situation has its own quirks.  Here’s my generic template for troubleshooting them.

First, I start with the holy trinity of IT Troubleshooting Questions:

1. What are you trying to do?

• What data do you want?
• What is the end result you want?

2. How does your result differ from what you expected?

• “I thought I could do a 7-way join on unindexed fields in tables with over 3 million records each in less than 2 seconds.  I’m pretty disappointed with Postgres’s performance.”

3. What changed?

• Has it always been this slow?
• Did this happen suddenly, or did it slowly degrade?
• If suddenly, what changed?  (“This started right after our last deploy.”)

RDS OS update

I’ve some more info for the AWS RDS OS update I mentioned last week.

The announcement states that this update will take an outage. In my experience, it’ll be the usual failover time for a Multi-AZ instance (a minute or so), and 8-10 minutes for no-Multi-AZ instances. According to my event logs, the entire update process takes 20-25 minutes.

Since I had n > 1 instances to handle, I preferred to review & schedule updates via the cli, because I can then write a quick bash wrapper for each piece & handle them in bulk.

Here’s the command:

aws rds describe-pending-maintenance-actions

To review upcoming maintenance for only specific instances, pass it a comma-separated list of instance names to the filters switch, like so:

aws rds describe-pending-maintenance-actions \
--filters Name=db-instance-id,Values=mydb1,mydb2

Here’s an example of what you’ll get back:

PENDINGMAINTENANCEACTIONS arn:aws:rds:[az]:[acct_no]:db:[dbname]
PENDINGMAINTENANCEACTIONDETAILS system-update 2016-02-26T19:41:22Z 2016-02-26T19:41:22Z Performance improvements and security updates

(The timestamps are in UTC, so you may have to do some math.)

Neither of those timestamps were in my maintenance window, so I dug into the docs  a bit further.

There are three possible dates available:
AutoAppliedAfterDate – AWS will apply the maintenance in the next maint window after this date
ForcedApplyDate – AWS will apply the maintenance at this time, regardless of maintenance window (eeeek!)
CurrentApplyDate – When AWS will apply it, if you’ve opted-in.

Without field headers, I can’t really figure out which two of the possible three dates are displayed. That “ForcedApplyDate” is a bit concerning, so I used the ‘output to JSON’ option to get some more info:

aws rds describe-pending-maintenance-actions \
--filters Name=db-instance-id,Values=mydb1 \
--output json

(excerpt):

"PendingMaintenanceActionDetails": [
  {
    "Action": "system-update",
    "Description": "Performance improvements and security updates",
    "CurrentApplyDate": "2016-02-26T19:41:22Z",
    "AutoAppliedAfterDate": "2016-02-26T19:41:22Z",
    "OptInStatus": ""
  }
],

So that clears that up, a little. Now to get this stuff scheduled!

aws rds apply-pending-maintenance-action \
--resource-identifier arn:aws:rds:[region]:[account]:db:[db-name] \
--apply-action system-update \ 
--opt-in-type next-maintenance

Since I now know which fields I want, I can use ‘output as text’ to verify my scheduling action, filtered with the –query switch:

aws rds describe-pending-maintenance-actions \
--filters Name=db-instance-id,Values=mydb1 \
--output text \
--query 'PendingMaintenanceActions[*].{OptIn:PendingMaintenanceActionDetails[*].OptInStatus,Date:PendingMaintenanceActionDetails[*].CurrentApplyDate}'

Output:

DATE 2016-01-22T17:04:00Z
OPTIN next-maintenance

(If you are wondering “how in Sam Hill did you create that query filter from that JSON output?”, wonder no more: here’s a link to the docs.  Getting the –query filter correct usually takes some back & forth with reviewing the JSON output, so don’t be discouraged if it takes a few tries.)

Scale 14x recap

I went to SCALE mainly for PostgreSQL@Scale – Joe Conway’s grown this annual add-on into a two-day, two-track event.

The new venue is a huge improvement, even though the walk from the hotel was a bit longer than I expected. There are a lot more accessible-on-foot food options.

—

For my session, I gave an updated version of last year’s “RDS Postgres: A Journey down the Amazon”.

If you’re using RDS and aren’t keeping up with your monitoring, please be aware of the current required OS update.  You can view & schedule required updates via the web console; here are the corresponding cli commands:

aws rds describe-pending-maintenance-actions \
--filters Name=db-instance-id,Values=mydb,mydb1,mydb2

aws rds apply-pending-maintenance-action \
--resource-identifier arn:aws:rds:[region]:[account]:db:[db-name] \
--apply-action system-update \ 
--opt-in-type next-maintenance

I find JSON and text output more useful for these types of tasks; I’ll cover that in a later post.

Accessing RDS Pg logs is kind of a sticking point for some people, me included. (I _really_ want an easy way to get them into Splunk.)
Here’s Denish Patel’s work using the rds cli, and a quick bash script I threw together, which uses the aws cli.

Based on the quick headcount I took at the beginning of the session, attendees were about 2/3 devs, 1/3 DBAs. We also had a couple of people from the Amazon RDS team; it’s always nice to know I have backup in the audience! ;)

A couple of folks had questions after my talk and I didn’t catch up with them in the booth. If that is you, feel free to leave a comment here, or email me.

—

Other talks I attended:
Jim Mlodgenski’s Debugging PL/PgSQL contained some good tips on use of RAISE NOTICE and what not to do. I installed pl_profiler and started using it the next afternoon.

Xof’s JSON Home Improvement included advice on when to use JSON vs JSONB, or maybe even neither :) If you’ve been reading my blog for any length of time, you know that these kinds of discussions just end in me making plans for more benchmarking…

Peter Geoghegan’s UPSERT use cases  covered a good range of material from beginning to “We can do WHAT now?” and made me realize I can always improve my SQL skills.

I’ve been hearing about ToroDB  but hadn’t attended one of Alvaro’s talks yet. Very basically, it’s a Pg-backed Mongo interface. They take the json data and explode it out into sub-documents. Then there’s a table that stores the structure _in json_. It both blew my mind & had me smacking my forehead “of course!”

It is in alpha, so this applies.
—

Slides for talks are (or will be) on the Pg wiki.  We do rely on speakers to load their own slides, so if you are after something in particular, please ask & I will remind them. :)

—

I’m already looking forward to next year, and hope I get to go ice skating then!

My “dev” environment

I’ve been using VMs a lot lately at my new gig. (VMWare, which is a bit slow and crashy for me on the mac.) It’s nice to know that I can muck about with things without having to worry about possibly having to completely rebuild my machine.

Here’s my standard minimal setup, when I’m building a VM from scratch:

Install OS (usually CentOS or Ubuntu), then:
– create a user for yourself if you did a server install
– add yourself to the sudoers file :)
– my personal .rc files

Additional apps/setup:
– VMWare tools:
./vmware-install.pl –default
(sometimes seems to require a restart)
– monofur
– configure your terminal profile
– synaptic/yum package manager
– any updates
– vim, if it’s not already installed
– Ubuntu: gconf-editor, then fix the dang window buttons!
– ntp
– git (include command-completion – obtain from git.kernel.org repo)
http://git.kernel.org/cgit/git/git.git/plain/contrib/completion/git-completion.bash?id=HEAD
http://git.kernel.org/cgit/git/git.git/plain/contrib/completion/git-prompt.sh?id=HEAD
– wireshark
– rrdtool
– trapgen
– latest postgres

Important notes:
You have to re-install VMWare tools after every kernel upgrade.
Snapshots are *not* backups, you actually don’t want to keep them lying around.