Today, I presented at PgOpen 2016 (Postgres conference) in Dallas, TX.
Here is the slide deck :
Today, I presented at PgOpen 2016 (Postgres conference) in Dallas, TX.
Here is the slide deck :
A couple of weeks ago, I had to deal with corrupted Postgres database cluster. At the end, we couldn’t able to recover some of the data but managed to recover most part of it. Having experience working with dozens of database systems, I’m pleasantly surprised to experience resiliency of Postgres database.
Kudos to Postgres Development team for building the most resilience database in the world
Here is my Postgres database recovery story
I'm posting the steps carried out during the recovery process for information purpose only. This post doesn't provide any guarantee that it will work for your use-case and/or environment.
Note: The actual database name has been replaced with “dbname” and actual table names with “tablename”.
One of the Postgres DB cluster database experienced disk level corruption thus we were hitting this error:
postgres=# \c dbname FATAL: could not read block 0 in file "base/16389/11930": Input/output error
Uh oh?? Really bad, isn’t it? Fortunately, it wasn’t mission critical system so we managed to take extended outage and work on partial recovery process because we didn’t want to loose all the data!!
Once we received the complaint, we immediately backed up corrupted database and created recovery cluster to bring up on different server so we can go through recovery efforts!!
As many of you know, the first option is to bring up recovery database cluster with zero_damaged_pages=on . You can set the value in Postgres config file and try to reindex system catalog:
reindexdb -p 5433 --system dbname reindexdb: could not connect to database dbname: FATAL: index "pg_index_indexrelid_index" contains unexpected zero page at block 0 HINT: Please REINDEX it.
Doh! Still, we could still not be able to connect to database !!
If you aren’t aware, you should note down that there is a way to ignore indexes at system level. We started up recovery cluster with ignore_system_indexes=true setting:
pg_ctl -D /data -o '-c ignore_system_indexes=true' restarted
dbname=# \c dbname
Yay! I could able to connect to DB now!
Let’s try to reindex the database…
dbname=# reindex database "dbname"; NOTICE: table "pg_catalog.pg_class" was reindexed 2016-08-22 15:53:14.179 PDT rhost=[local] app=psql:user=postgres:db=dbname:ERROR: could not create unique index "pg_statistic_relid_att_inh_index" 2016-08-22 15:53:14.179 PDT rhost=[local] app=psql:user=postgres:db=dbname:DETAIL: Key (starelid, staattnum, stainherit)=(2608, 5, f) is duplicated. 2016-08-22 15:53:14.179 PDT rhost=[local] app=psql:user=postgres:db=dbname:STATEMENT: reindex database "dbname"; ERROR: could not create unique index "pg_statistic_relid_att_inh_index" DETAIL: Key (starelid, staattnum, stainherit)=(2608, 5, f) is duplicated.
As the table is corrupted with duplicate entries, let’s find out and fix them.
dbname=# select starelid, staattnum, stainherit from pg_catalog.pg_statistic where starelid=2608 order by 2; starelid | staattnum | stainherit ----------+-----------+------------ 2608 | 1 | f 2608 | 2 | f 2608 | 3 | f 2608 | 4 | f 2608 | 5 | f 2608 | 5 | f 2608 | 6 | f 2608 | 7 | f (8 rows)
Let’s remove one of the entry based on XMIN :
dbname=# delete from pg_catalog.pg_statistic where starelid=2608 and staattnum=5 and xmin=1228447; DELETE 1
Restart REINDEX but it failed again!!
2016-08-22 16:01:29.698 PDT rhost=[local] app=psql:user=postgres:db=dbname:ERROR: 1 constraint record(s) missing for rel tablename 2016-08-22 16:01:29.698 PDT rhost=[local] app=psql:user=postgres:db=dbname:STATEMENT: reindex database "dbname"; ERROR: 1 constraint record(s) missing for rel tablename
Let’s try to vacuum analzye the table
dbname=# vacuum analyze tablename; 2016-08-22 16:04:01.282 PDT rhost=[local] app=psql:user=postgres:db=dbname: 1 constraint record(s) missing for rel tablename 2016-08-22 16:04:01.282 PDT rhost=[local] app=psql:user=postgres:db=dbname:STATEMENT: vacuum analyze tablename; ERROR: 1 constraint record(s) missing for rel tablename
hrm…it’s still complaining about constraint
let’s disable constraint check….
dbname=# update pg_class set relchecks=0 where relname='tablename'; UPDATE 1 The above update fixed the the constraint error
Let’s reindex the database again!
dbname =# reindex database "dbname";
Yay, Reindex is successful.
Once the reindex is successfully completed, we restarted recovery cluster without zero_damaged_page and ignore_system_indices settings.
As the database is corrupted, it makes sense to kick off the pg_dump on the database … we kicked off the pg_dump but it was still showing some of the sequences with errors!!
/usr/lib/postgresql/9.4/bin/pg_dump dbname -p 5433 -Fc >recovery_dbname.dmp 2016-08-22 16:22:09.517 PDT rhost=[local] app=pg_dump:user=postgres:db=dbname:ERROR: invalid page in block 0 of relation base/16389/2825248 2016-08-22 16:22:09.517 PDT rhost=[local] app=pg_dump:user=postgres:db=dbname:STATEMENT: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL ELSE min_value END AS min_value, cache_value, is_cycled FROM XX_id_seq pg_dump: [archiver (db)] query failed: ERROR: invalid page in block 0 of relation base/16389/2825248 pg_dump: [archiver (db)] query was: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL ELSE min_value END AS min_value, cache_value, is_cycled FROM XX_id_seq
We had issue recovering a couple of tables but we managed to recover the most of the tables in the database !
This was our Postgres database recovery story.
Hopefully, it will help someone in case they fall into corruption situation. Any feedback or other ideas are welcome
Last week, I received request to increase PostgreSQL’s config parameter default_statistics_target on one of the very large DB clusters. The developers filled a ticket to increase the default_statistics_target to 10,000 at cluster level because one of the queries has started full sequencial scan instead of index scan.
By default, Postges cluster sets default_statistics_target to 100 but you can set up to 10,000. This parameter defines how many values are to be stored in the list of most common values, and also indicates the number of rows to be inspected by ANALYZE process.
Out of curiosity, I dig into Postgres ANALYZE code to figure out how many rows is being scanned by ANALYZE based on default_statistics_target value. As you can see in line 1763, ANALYZE inspects rows up to multiple of 300 & default_statistics_target.
1763 stats->minrows = 300 * attr->attstattarget;
If the default_statistics_target value is 100, ANALYZE could scan up to 300 * 100= 30,000 rows to gather accurate statistics.
As per Postgres documentation on ANALYZE, if you increase the value blindly at cluster level it could hurt the smaller queries because query planning time could increase significantly by setting the default_statistics_target value to maximum, which can eventually degrade the peformance for simple queries. The next logical step for me to find out optimal value for the default_statistics_target for the specific table with slow query. I will probably start with increasing value to 1000 and increase/decrease value based on EXPLAIN ANALYZE query results.
When you try to tune default_statistics _target for your database server, hopefully this finding will help. Let me know, if you have alternate views or idea for tuning the setting. Thanks!
While casual discussions with Postgres users at conference and/or online, I came across this question for at least 3-4 times; “Is there a Slack channel for Postgres?” The answer was “No”.
The idea of bringing up Slack channel was cooking in my mind for a long time but i did not have time to execute it! Now, I have brought up Postgres Team Slack channel in end of Nov 2015 and there are already 100+ users in the channel. You can sign up here http://postgres-slack.herokuapp.com/
Following are the benefits you will get by connecting to Slack channel:
Hope, this channel will be useful to drive more people to discuss postgres at scale!!
If you are already using Postgres IRC channel, I’m not insisting you to use this new slack channel. I have come up with slack channel for mainly for slack users who are already using it for other purposes! Thanks for understanding.
On #postgresql IRC channel, someone was having problem bringing up standby database server from backups taken using pg_basebackup and recovery.conf was configured to use replication slot.
The reporter was following below steps:
SELECT * FROM pg_create_physical_replication_slot('standby')
The standby server was throwing following error during startup ..
requested WAL segment 000000010000000C000000AE has already been removed.
Question from Reporter:
Should I set wal_keep_segments to some non-zero value if I want to use replication slots because while reading docs I understand that I shouldn’t set wal_keep_segments if I use max_replication_slots?
Short answer :
As of now, pg_basebackup doesn’t have support for replication slot so it doesn’t have details on status of WAL files being created or removed.
While the feature is being worked on, you can use one of the following solutions to get around the problem for now.
On primay database server, set wal_keep_segments to some sane value relative to database traffic volume and time to make & transfer backup to bring up standby server. This setting will keep # of WAL files around on the primary server and will not delete them so when you bring up standby server using replication slot, it will start applying WAL files and eventually connect to physical slot created.
Create slot before taking backup and use the slot at the least once so primary db server will keep the WAL files around.
/usr/pgsql-9.4/bin/pg_receivexlog -h primary-db-server -p 5432 -U replication –S 'standby1' -n -v -D /var/lib/pgsql/9.4/archive
I would prefer Solution #2 over Solution #1 because you don’t have to figure out optimal value for wal_keep_segments. Plus, you can use pg_receivexlog method for permanent archive solution. If you want to get more details, I will be presenting tutorial on replication topic “Out of the Box replication in Postgres 9.4” at Postgres Conference in San Francisco on Nov 17-18th. I will be provide hands on tutorial to setup robust replication for production db servers without using any third party tools in Postgres 9.4
Let me know, if you have better solutions than I discussed in the post. I’m looking forward to see you at the conference.
Last year, I have presented on Postgres RDS in Amazon at couple of conferences but I haven’t got change to follow up on my experience working with RDS after them. If you aren’t aware, I recently changed job & started working at WithMe, a disruptive start-up that focuses on three primary solutions for commerce, including WithMe Business, Financial and Retail.
At WithMe, we are using a fair bit of Postgres RDS instances. After joining, I reviewed the setup and found out the pgabadger log analysis is not setup on the database servers. Even though the databases are still in development phase, it makes sense to have reports that will help to provide more insight to software developers on the slow queries. I had other projects (schema(s) design, database migrations,DW system) going on so i wasn’t able to spend time identify and tuning queries to assist software engineers to expedite their develpement so we decided to prioritize log analysis project.
At previous job, I have setup pgbadger reports on number of servers but most of the time I had to deal with real db servers or at the least had ssh access on the server. On the RDS, you can only have psql access, no shell level access on the instance. Additionally, You have to deal with AWS tools and access methods.
I followed following steps to setup pgbadger reports for Postgres RDS instances:
[denishpatel@pgadmin1 ~]$ aws configure –profile oregon
AWS Access Key ID [None]: XXX
AWS Secret Access Key [None]: XXX
Default region name [None]: us-west-2
Default output format [None]: text
[denishpatel@pgadmin1 ~]$ aws rds describe-db-instances –profile oregon –output table
# pgbadger reports
0 7 * * * ~/run_pgbadger_rds.sh -i devel-postgres -r us-west-2 -c 1 >/dev/null 2>&1
15 7 * * * ~/run_pgbadger_rds.sh -i prod-postgres-rds -r us-east-1 -c 1 >/dev/null 2>&1
In case, you are tasked to setup pgbadger report on Postgres RDS instance, this post might help ! I haven’t spent a lot of time writing a script so script provides very bare minimal to run reports but it can have a lot of improvements. Hope to see your contribution to improve the script
As always, I’m looking forward to see some comments from your experience working with Postgres RDS
Postgres 9.4 introduced an awesome feature Replication Slots. This allows you to implement Postgres replication without using any external archive management tools.
Yesterday, I presented three hours long tutorial on “Out of the box Postgres 9.4 Replication using Replication slots” at PgCon (Postgres conference) in Ottawa,Canada. If you want to follow along slides with VM, you can download it from the link posted on the conference tutorial schedule page.
Here is the slides deck:
As title suggests, if you are running Postgres in your environment, it is very important that you plan to upgrade Postgres with the latest announced release.
Yes, you should plan to upgrade again.
Postgres released data corruption and security bug fix release on May 22nd and follow up release on June 4th to fix some of the issue introduced in previous release. However, the June 4th release did not fix the multixact wraparound bugs (Thread1, Thread2 ) introduced in recent versions. The wraparound bug could cause problem starting Postgres after the crash , so it is critical to apply. The wraparound bug is more critical for 9.3 & 9.4 but it is very good idea to upgrade for other versions to make sure there isn’t any problem. Read the release notes for the further details/explanation.
Special care should be taken regarding vacuuming , if you ever ran Postgres 9.3 version and used pg_upgrade for the production databases.
Yes, absolutely!! You are running EOL or unsupported version of Postgres. It is very important that you upgrade Postgres database to supported release because it is most likely to hit any or all of the security and/or data loss bugs fixed in recent Postgres releases.
Yes, you should upgrade to the latest minor release (3rd digit in version numbering). The minor release only requires installing new binaries and restart the database. It does _not_ require to use pg_dump/restore or pg_upgrade.
You should be upgrading to the following latest relevant Postgres release for your environment ASAP:
Postgres releases support can be found here. It is worth to note that , if you are running Postgres 9.0, you should plan for major version upgrade (most likely to latest Postgres 9.4.X release) before the holidays season begin because Postgres 9.0.X will be EOL or unsupported by September,2015.
Hope this will help to convince your manager(s) for the Postgres database upgrade!
Today, I presented on “Postgres in Amazon RDS” topic at Postgres Open Conference in Chicago. Here is the slide deck:
I thought to share conference details that I’m going to attend next month. If you aren’t aware but interested attending any of them.
The speakers and talks lineup is looking interesting for both conferences. If you haven’t registered for them, it’s not too late yet!! If you are planning to attend anyone of them, looking forward to meet you in-person.
See you soon!