Category Archives: conference

Let’s hangout w/ Postgres people on Slack!

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:

  • If you are using Slack for other communication, it will be easier for you to bring up another company/team on the Slack application.
  • Slack provides integration with number of services i.e Twitter with #postgres and #postgresql hashtags, postgres weekly news and @postgres Twitter handle messages
  • I’m yet to integrate Slack with Postgres gitrepo because I don’t have access to add post-commit hook on the gitrepo. Please email  me @ denish.j.patel@gmail.com, if you can help me to setup integration for Postgres gitrepo . I will provide instruction and web hook url to add into github repo settings.
  • You can access Slack from your mobile devices
  • If you aren’t connected, you still have history of the chat

Hope, this channel will be useful to drive more people to discuss postgres at scale!!

Disclaimer: 

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.

Postgres Replication Slot & pg_basebackup

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:

  1. Set max_replication_slots=5, wal_keep_segments=0 and restart primary database
  2. create replication slot :
  3. SELECT * FROM pg_create_physical_replication_slot('standby')
  4. Call pg_basebackup -D – -Ft | bzip2 > backup.tar.bz2
  5. copy backup.tar.bz2 to standby server
  6. start postgresql on 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.

Solution 1:

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.

Solution 2:

Create slot before taking backup and use the slot at the least once so primary db server will keep the WAL files around.

  1. On primary db server, create slot
  2. connect to slot using pg_receivexlog
  3. /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
  4. You can kill the pg_recievexlog process
  5. Take backup using pg_basebackup
  6. copy the backup and startup  standby database
  7. The standby database should be able to connect to slot now!
  8. Don’t forget to drop the slot otherwise Primary DB server will keep the WALs indefinitely

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

If you haven’t registered, this is the time to register because the conference schedule looks amazing!  You could still register with the discount code SeeMeSpeak which provides a 20% discount.

Let me know, if you have better solutions than I discussed in the post. I’m looking forward to see you at the conference.

 

Upcoming Conferences in September

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.

  1. PostgresOpen at Chicago
    • Postgres conference (Sept 17th – Sept 19th)
  2. Surge at National Harbor,MD
    • Scalability and Performance Conference  (Sept 24th – Sept  26th)

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!

Postgres Security & PGConf NYC 2014

As per my knowledge, I haven’t heard or attended  Postgres conference with dedicated “Security” track . Rarely, you find one or two talks about security over couple of Postgres conferences but  never a dedicated track !  It is  indisputable to say that  Security is one of the most critical areas to consider for selecting  database for the application. I’m really excited  to attend and speak  about Postgres Security features at PGConf  NYC 2014 conference, which is going to be held on April 3-4, 2014 in New York and will have dedicated “security” track ! Hats off to organizers  🙂

My employer, OmniTI, is one of the Gold sponsors for the event.  If you are planning to attend the conference, please use code “OMNITINYC14” at checkout, it will allow you to receive a 10% discount on both the Early Bird and Regular ticket prices – the code is valid through March 15th.

If you want to make friends in Postgres community and eager to learn  advance features  in Postgres & more specifically Security features,  you probably don’t want to miss out this rare opportunity!

Send me note, if you want to catchup in-person in NYC.  Looking forward to see you there !