Press "Enter" to skip to content

Category: PGSQL Phriday

Tips on Navigating Postgres Documentation

Published 2023-03-03 by Kevin Feasel

Laetitia Avrot dishes dirt on Postgres documentation:

I could have created a very easy post with quick tips on psql, like how to disable this horrible pager the “ancient” Postgres contributors insist on keeping on by default (BTW, it’s \pset pager off, you’re welcome, you’ll thank me later), but as I wrote an entire website on that exact topic, I thought I needed to find something else.

So here is my topic: how to use the Postgres documentation! Yes, that documentation content is great, but no, that documentation is not easy to navigate at first.

Click through for tips on the best ways to navigate through this documentation, as well as important pages and topics based on your use case and role.

Comments closed

Thoughts on Software Development and Postgres

Published 2023-01-09 by Kevin Feasel

Ryan Lambert thinks about software development:

Question: Do you store your SQL code in GitHub (or other source control, e.g. GitLab)?

Yes! All mission critical SQL I am involved with is saved in a Git repo, either public or private depending on the project. The use of source control for mission critical code (SQL, Python, etc.) is non-negotiable. A good portion of my “not trivial” code is also stored in source control, with my trend leaning towards more code in source control. The trivial code isn’t worth the effort of putting it into source control or the bloat it creates in those projects.

Read on for more.

Comments closed

CI/CD and Postgres

Published 2023-01-09 by Kevin Feasel

Ryan Booz thinks about Database DevOps:

At the core, the biggest problem we needed to solve was making our database change process visible to the whole team. We had to modify our processes and the expectations we had of individual people within the team to deliver better, more consistent value at the data layer.

The tools we chose for our circumstances don’t apply completely to PostgreSQL because they were SQL Server specific. But the process and benefits of building a “database as code” mindset within the team accelerated our velocity and quality. And Joe didn’t have the weight of the world on his shoulders.

Ryan includes links to additional resources and tells an interesting story along the way.

Comments closed

Moving Away from pg_dump

Published 2022-11-07 by Kevin Feasel

Pat Wright explains why pg_dump isn’t the best solution for backup and restore:

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. 

Read on for the tradeoffs around tools and various thoughts from Pat.

Comments closed

The Importance of Validating Postgres Backups

Published 2022-11-07 by Kevin Feasel

Grant Fritchey brings an important insight:

I’m very much just beginning my journey of learning PostgreSQL. I’ve been documenting that learning over here at Simple-Talk (more on the way there), including backups. For this post, I’m not going to tell you about my “experience” maintaining a PostgreSQL backup routine because, well, there isn’t any. Instead, I have something else to say about backups that I learned, the hard way I might add, while working in SQL Server, that is 100% applicable to PostgreSQL.

Click through for Grant’s thoughts. The “what” makes sense. The “how” is the important part.

Comments closed

Thoughts on Postgres Backups

Published 2022-11-07 by Kevin Feasel

Ryan Booz shares some thoughts on backups in Postgres:

To be honest, I feel pretty unqualified to talk much about backups in Postgres, partially because it’s been a couple of years since I’ve had to manage the uptime of an application. As PostgreSQL has grown in popularity and usage, there is a lot of effort being put into tools like pgBackrest, pgBarman, and some newer solutions like pgcopydb (ok, I realize this isn’t billed as a “backup” tool, but…).

What I can share are a couple of things I’ve learned about restoring clusters and databases, particularly given the extensible nature of Postgres and the variety of tooling.

Read on for thoughts on a variety of topics.

Comments closed