What is the PostgreSQL community to me? #PGSQL Phriday #003

What is #PGPSQLPhriday

For PGSQL Phriday #003 I asked us to consider the PostgreSQL community, what it means to us, and how we would get started as a new users.

I started this topic for #PGSQLPhriday with ulterior motives.  I wanted to hear about the community from other people in the community so that I could find more ways to make to improve the PG community.  Yes, I did just say community way too much! 

I’ve worked in many different communities in my career and I love to help and support many communities. When I started in PG about 5 years ago I started looking for the community and unfortunately didn’t have the best experience finding it.  Yes, I found the resources you will see listed below, but I didn’t *feel* like I had found the community.   

I started attending conferences, communities usually shine at conferences more than anywhere else.  Unfortunately at my first few conferences I still wasn’t really finding the community I was looking for.  So I did what any average extroverted introvert does.  I volunteered!  

See I have a certain set of skills….

Particular set of skills - KPSQ-LP Fayetteville 97.3 FM

No not those skills….

I’ve helped to build a few communities and put on conferences in my “spare time”.  So I figured I would volunteer to focus on ways to make the community better.  2 years into this role now and I feel a lot better about the community when I attend events.  I hope we can greet so many new people to the community as well. 

Ok, Mr. Long Winded!  You haven’t answered the question? 

What is the PG community to me?   

The PG Community to me is a place where you can go to meet people that have gone through many of the same nightmares as you, meet people that want to reach out and help with your problems, people that will come to your aid when you have a down server or some sort of question you just can’t answer. The community is where you go when the engineers keep asking “I don’t understand why I can’t have 64 Jsonb fields on one table….I see no issue to query that directly with 100 million rows of data”.  Or if you are the engineer asking this question come join the community and let us tell you how you can do it better. 

Community is a resource and a group of people you learn with, and figure out solutions with, and while you are helping the community grow you are growing as well.  

Second question? How do you get started?


Well that’s easy! Here is a step by step.

  1. Scroll to bottom of this page.
  2. Click on links.
  3. Join slack, a user group or attend a conference!
  4. Ask questions and help answer questions
  5. Perhaps present at a local user group! Or conference!
  6. Make millions of dollars!!!!! Ok for legal reasons let me be clear. This *may* not happen to you, but what if it does?

I look forward to growing the PG community with all of you! I hope that we can create a welcoming environment for the thousands of new Postgres specialists out there.  

Listed below are my favorite resources related to the community. Sorted by my most used first.  

Slack Channel 

PG US User groups 

PG Conferences – link to all PG Events. 

Pg NYC  –Amazing NYC conference. 

PG Chicago 04-20-23 – First time Chicago event! 

PG Mailing lists 

Advertisement

PGSQL Phriday #003: What is the PostgreSQL community to you?

Picture taken at NYC PG Conf 2022

I am excited to continue on the #PGSQL Phriday blogging series.  This series was first started by Ryan Booz earlier this year. We are now on the third edition, which is all about the community.   

What is the PostgreSQL community to you?  

I personally am relatively new to the PG community. I only started attending events and working with PG about 5 years ago.  I’ve talked to a lot of different people in the community and I’ve found many different people have different ideas of what the community is and where it is.  

I would love to hear more varied views and opinions on this topic. Here are some ideas if you need help getting started with a blog post.  

  • List of resources you commonly use in the community? 
  • Favorite event(s) that you attend to make you closely connected with the community? 
  • A story of help/work provided by you or someone else in the community? 
  • How would you get started in the community?  

How does #PGSQLPhriday work?

  • Post your blog post about “What is the PostgreSQL community to you?” by Friday, December 16th. December 2nd.
  • Include “PGSQL Phriday #003” in the title or first paragraph of the blog posting.
  • Link back to this blog posting, for example: “Pat here is my voice on Community!  ‘PGSQL Phriday #003′”.
  • Announce your blog post in one or any of these places:
  • Interact with other blog posts about this topic!  We hope to see conversations happening, slack, Twitter, or directly in blog comments works as well.

PGSQL Phriday # 002: Postgresql Backup and Restore 

Always have a backup! You might need this if you are working on a restore as well.

This blog post is related to #PGSQLPhriday. The original post by Ads can be seen here.

Here is the question that Ads has asked in the post.

Describe how you do backups for your PostgreSQL databases.

Which tool(s) are you using, where do you store backups, how often do you do backups?

Are there any recommendations you can give the reader how to improve their backups?

Any lesser known features in your favorite backup tool?

Any new and cool features in a recently released version?

Bonus question: Is pg_dump a backup tool?

If you go back to ads’ original blog post, he asks, “Backups? Do you need that?”.  When I read this as the topic for #PGSQLPhriday, I thought to myself this guy is crazy,  he must have had too much ice cream if he believes we don’t need backups!  But then I realized he was just using that as a headline to get me reading more.  Thank you ads for helping my heart to skip a beat a little.  I owe you a good ice cream at the next event for that one.   

Let’s Talk Backups

I’m still fairly new to Postgres having only started about 5 years ago. I started with Pg9.6 and we quickly moved that environment to pg10.   When I arrived at this company,  pg_dump was the only backup we were using.  The DB at that time was still around 50GB, it was reasonable to do the backups in a timeframe that worked for us.  A dump was done every night and stored off to another server.   After some time we started to test Pg_basebackup. This allowed us to full server backup each night. It was a huge improvement as far as speed and ability to handle much larger data sizes. 

The downside to this approach is that you have to restore the full server directory to another server or over the top of your existing server.  In the world of the DBA, one of the more frequent things you do is restore just a  table that gets dropped by a DBA, Engineer, Sysadmin, or even the CTO sometimes.  When you have to simply restore one table the base backup makes that pretty hard since you HAVE to restore the entire Server/Directory to another location.  

Now don’t get me wrong it’s not easy to do that with PG_dump either, but with Dump at least you can just point that to your same server and just a new DB name.  That means it’s usually a lot faster to restore just that table. Also with the Dump, there is a good chance it will restore the data quickly. If you already have the structure of the table you can simply restore the data to the original database and not wait for things like Indexes or keys to be restored from the dump.  

Regardless of the tools or the Database (SQL Server, PG, MySQL, ES) here are some key items I always make sure of in any of my Database situations 

  1. Test all Backups!  You have NOT backed up if you don’t test the backup by restoring it.  This doesn’t have to be every file but you should have an audit restore running every X days/week that can test a backup file for you.  Just do it! 
  2. Make sure others can backup/restore and write down the Backup and Restore procedure.   Even if you have a seasoned team of DBA’s you never know when the whole group will be unavailable.  Sometimes you need the person that knows the least amount about restoring data to be able to restore the data.  We always maintain documentation on how to restore the DB and make sure several teams in the company have both the permissions and the understanding of how to do it.  
  3. Know how you are planning to restore the DB.  99.99999% of the time you are not going to restore over the top of the DB/Server you are already using.  Most of the time you are going to restore to a new server/new system/different DB.  Think about this when you are designing your backup and restore process. A lot of tools/systems will talk about how quickly they can put the whole drive back in place from just a few hours ago. But rarely does a customer want everything back to a few hours ago. Typically it’s just one aspect of the Database that’s been impacted and you don’t want to roll back all the other pieces of the system. When designing your strategy you should be thinking about what a restore looks like and what you typically will use it for. 
  4. Don’t just backup the DATA!   Many database systems have configurations and settings that are key to making the database work properly. Perhaps you have a custom configuration DB in your system that you need to make the application work.  In PG We would back up the PG conf file as well with our backup scripts.  This allowed us to restore the server if needed.  You don’t need to back it up as frequently as the data files but it should still get backed up.  
  5. Stop backing up to the server! Yes, I still see this at customer locations. It’s perfectly fine to write the backup to a local disk and THEN synchronize it to the cloud, s3, or tape drive(they still exist).  Just make sure you are doing that step and have alerts if that step fails.  If you go back to #1 in this post you should run your test restore from your cloud backups, not off the local disk.  If you only have backups on a local server and a local drive even if it’s in a data center do yourself a favor and get an s3 bucket and synchronize the backups to the bucket.  Recently I had a customer not be able to retrieve the data from the data center because the data center got hit with ransomware. They locked everything down and wouldn’t let anything out. If we had a backup in s3 or on the cloud we would have had a copy and could have kept moving forward and rebuilding. This almost put the company out of business but luckily we found a backup outside the data center that was a few months old.    

I have so many more stories I could tell about backups and restore!  I will keep this post relatively short and dedicate a future post related just to these items.  I hope this list will help you to build your backup strategy.  I would love to hear about issues you have had with backups and restores!