Check PostgreSQL Version: Running and Managing PostgreSQL

Every SaaS builder needs to operate multiple systems centered around their database. For most, PostgreSQL is the best database option. Yet, for non-developers, managing a database can feel like a challenging task. Knowing how to regularly run queries for in-depth insights can quickly get overwhelming. Aside from that, one should know how to track the database’s health, make sure that updates don’t affect performance, set up SSL, and check PostgreSQL version.

In this blog post, we offer a short guide to set up, configure, and optimize PostgreSQL for your SaaS needs. We also go over some basic database maintenance tasks. The article presents a series of short chapters with practical examples to help you build the confidence to work independently. Let’s get started!

Understanding the PostgreSQL Environment

If you’ve worked on any SaaS before, you’re probably familiar with developers telling you that a development or change is “on stage” or “on production”. What they actually refer to is the application and its databases, which exist in multiple environments, each with its own purpose.

Production is the live application and database that the public has access to - it’s the version of your product that your customers see and use. So changes on the “prod” level occur at the final step, as any mistake here can affect the user experience.

Staging is a replication of Production that is not yet live. A safe version where you should make any final tests before releasing to Production. The staging database typically doesn’t contain production data for security reasons, but that depends on your architecture.

Development is where engineers introduce new features and experiment without any risk to customer data and performance impact. It's a sort of playground, where your ideas take shape.

The above information does not require any SQL to understand, but simplifies communication with developers. Hence, knowing these basic terms can help avoid misunderstandings and help you keep a finger on the pulse.

Check PostgreSQL Version

Now that you know the different environments of your database, let’s check how you can perform a PostgreSQL version check. This, too, is very simple. Just run this query:

1SELECT version();

Alternatively, you can enter the following code in the terminal if you have command-line access to the server:

1psql --version

Any of the two commands will enable you to check PostgreSQL version. You will also see additional details, like the server build. Knowing which version you currently operate can prevent mistakes, like using incompatible SQL queries or stored procedures.
To give a SaaS example, imagine that the latest feature you are looking to add uses JSONB indexing. If your current Postgres version does not support advanced JSONB, the feature could break. Knowing your version ensures this doesn’t happen.

A supplementary command that can add value includes:

1SHOW server_version;
2SELECT current_setting('server_version');

This command helps you check what the current version of your database is, regardless of the environment you are connected to. When you check PostgreSQL version before making important changes, like installing additional extensions or migrating, you avoid making mistakes that can cost you later.

How are these 3 options different?

All three aforementioned commands allow you to check the version of your PostgreSQL database. Yet, each of them slightly differs in its format and use case:

SELECT version() - Returns a single text string which includes OS, compiler, and sometimes extension/packaging information. It is meant for human inspection, and thus a good option for quick, manual checks.

SHOW server_version; - Reads the server_version GUC in a clean form. The output contains only the version number, which makes it ideal for machine parsing. The SHOW command is specific to PostgreSQL configuration and not valid in all SQL situations.

SELECT current_setting('server_version'); - Returns the same server_version is the SHOW option, but in a text format. Can be used in expressions and functions and paired with other queries since it behaves like a typical SQL function. Often paired with current_setting fr a numeric version (easier for numeric comparisons).

Connecting to Your Database Safely

When you want to access a PostgreSQL database, you will need a connection string. This is a single line of code that contains the database address, username, password, and, on occasion, the port. It can be compared to the login data of your bank account - whoever has this information gains access to everything. An exception can perhaps be made when you’re looking to hire someone and want to temporarily give them access to the database (for a test task, or homework assignment). In this situation, you can create a limited permission user who, through the connection string, will only have access to what his role allows. The code below is an example of a regular connection string:

1postgres://myapp_user:supersecretpassword@prod-db.mycompany.com:5432/myapp_prod

You can read the information on this string as follows:

- postgres:// refers to the database type (Postgres = PostgreSQL)

- myapp_user refers to the username

- supersecretpassword is the password (use URL encoding if using special characters)

- prod-db.mycompany.com is the hostname, where you enter the credentials

- 5432 is a number that refers to PostgreSQL’s default port

- myapp_prod is the name of the database

SaaS Founders don’t need to manage these credentials, but it’s important to understand how to read them and avoid common mistakes. One of the most common mistakes, in this case, is sharing passwords over messaging apps. This should be avoided, and effort should be made to store these in secret managers (like AWS Secrets Manager or HashiCorp Vault). When talking with your developer, you can ask questions like “Is this connection string in our secrets manager?” to show that you value security and are aware of the risks involved.

Managing PostgreSQL Configuration

The postgresql.conf file, also known as the PostgreSQL configuration file directory (postgresql.conf.d), controls a range of important database parameters, like server memory, logging, and connections. For non-developers, knowing how to review key settings can give you the needed insights to prevent downtime and optimize performance.

For example, if your product experiences sudden traffic jumps, checking:

1SHOW max_connections;

and adjusting max_connections in postgresql.conf ensures that new users can access the environment by keeping the database responsive. At the same time, log_min_duration_statement helps you locate slow queries without having to go through the full logs. You can then reload the changes you made without restarting, by using this command:

1SELECT pg_reload_conf();

Data definition: Deleting, Dropping, and Renaming

Before diving into this slightly more technical section, check a more expansive collection of functions in our article: Mastering Dates, Text, and Analytics in PostgreSQL: 15 Essential Functions for Everyday Queries.

The following commands are common in practical maintenance tasks:

Drop triggers:

1DROP TRIGGER trigger_name ON table_name;

Delete with joins:

1DELETE FROM orders
2USING customers
3WHERE orders.customer_id = customers.id
4AND customers.status = 'inactive';

Rename index:

1ALTER INDEX old_index_name RENAME TO new_index_name;

To give a couple of common examples, imagine deleting data from a sandbox environment without affecting Production. Using the PostgreSQL delete from join function helps you remove such records safely. In the same way, you can use a PostgreSQL drop trigger to prevent automated actions while making schema updates.

Backups & Disaster Recovery

Having gone through some basic commands, let’s briefly discuss backups, too. A backup is an important part of protecting your data and helps you restore your database to a safe state in case of unexpected disasters. Since you don’t need to run these backups yourself, the following information is all you need to remember:

Logical Backups (pg_dump) – This tool creates a backup file of your database in a readable format. Your developers usually run it as part of an automated script or on a separate server. Logical Backups can be restored on any PostgreSQL database. These are great for smaller databases.

Physical Backups / Snapshots – These are handled by your cloud provider or your Kubernetes/PostgreSQL hosting platform. All you need to do is schedule them and recover them using a management console. These are better for large databases that rely on automated backups.

Point-in-Time Recovery (PITR) – These are also handled by your hosting platform and accessed from a management console or through an automated script. It backtracks the database to a specific moment, for example, right before an accidental deletion.

In short, backups are stored outside the database, and it’s important that one of the above systems is put in place and tested regularly. That said, you don’t need to run these yourself - it is usually the job of the developer.

Monitoring and Observability

This is relatively self-explanatory - as a Founder, you always need to monitor the health of your database. By doing this, you notice problems before your customers experience them, and get insights into the performance of your database. Here’s what you need to keep in mind:

CPU & Memory – Indicates whether the server has enough capacity or is struggling. High consumption of resources can slow your app.

Slow Queries – We briefly discussed these above. Some queries can slow down your app’s performance. In such cases, developers can optimize them.

pg_stat Views – Postgres databases have built-in statistics tables that track important activities. Referring to these tables is useful when looking for potential issues.

Alerts & Notifications – You can automate monitoring by using tools that send email alerts when an issue is spotted. You can then inform the developers to take immediate action.

Similar to backups, monitoring is not a part of your daily tasks, but an important component that needs to be in place. By doing so, your SaaS continues to operate as it should, without unexpected surprises.

Security Essentials for Non-Dev Founders

Another component that needs to be set up properly is that of database security. Non-dev Founders often overlook it for the sake of speed, but understanding the basics can prevent very costly problems down the road. The following terms help you ask better questions to developers and keep your data safe:

Roles – Each database user has a specific role, which determines what data they can access and adjust. A non-technical person might be able, for example, to check PostgreSQL version, but won’t be able to change the core functions of the app.

Permissions – Following on the above, roles get permissions which determine access to information or actions, like reviewing data or making adjustments.

TLS Connections – This is a security function that encrypts the app-database connection so it cannot be hacked.

Managing Credentials – As discussed in the Connect String chapter above, sensitive information should be stored safely in secret managers and not shared via email or added to sheets.

Least Privilege – Offer each database user only access to the extent that their job requires. This minimizes problems in case credentials are compromised.

PostgreSQL Change Data Capture and Stored Procedures

Change Data Capture (CDC PostgreSQL) monitors data adjustments for analytics. In SaaS databases, this can be useful if you want to log subscription upgrades to a table without making changes to your main workflow.

Stored procedures make your life easier, as they automate repetitive tasks. The following PostgreSQL stored procedure example is quite common:

1CREATE OR REPLACE FUNCTION log_update()
2RETURNS trigger AS $$
3BEGIN
4   INSERT INTO audit_table VALUES (NEW.id, NOW());
5   RETURN NEW;
6END;
7$$ LANGUAGE plpgsql;

This command tracks audit trails or trigger notifications in situations where data is adjusted. It comes in handy for SaaS apps, as it tracks user actions or payments, offering insights without manual reporting.

Migrating and Integrating Databases

If you’re planning a migration, we have a detailed migration checklist for your database. Feel free to review it for step-by-step guidance and best practices.

In this article, we only touch upon the basics. When a database transition takes place (e.g. migrate MySQL to PostgreSQL) it’s always best to start with a small dataset. When you have managed to migrate a dataset, you can then continue doing this piece by piece.

Thankfully, tools and scripts exist for schema conversion. When migrating to another database, therefore, remember to do the following:

Adjust data types so that they are compatible with Postgres

Confirm triggers and stored procedures

Make sure that the PostgreSQL version supports all features

To give an example of data adjustment, DATETIME in MySQL will need to be changed to TIMESTAMP in PostgreSQL. You will also need to validate the time zone settings to prevent reporting issues later. If your schema is optimized correctly, integration with reporting functionalities or analytics becomes a lot easier.

Long-Term Maintenance & When to Hire a DB Specialist

So far, we’ve looked at actions you can take when dealing with a small database. As your SaaS grows and your database expands, managing PostgreSQL becomes harder. At times, it might be in your best interest to rely on external help when it comes to the management of your database.

When to outsource DB management – If you operate a lean startup and your database requires regular maintenance, consider hiring a specialist who can manage things for you on a part-time basis.

When to onboard a part-timer – If your company has reached a level where you require occasional migrations, performance optimization, or expanding the functionalities of your database, you could hire a part-time Database Administrator.

When it’s best to ask for help – As your user base and product grow, databases get larger and more important. A good rule of thumb is to ask for help when you feel that you no longer have a full overview of the things you can safely manage. This is especially true for non-technical founders.

Do More & Stay Ahead with TalkBI

Even with detailed guides like the one you are reading, monitoring and interacting with PostgreSQL can quickly overwhelm the multi-tasking Founder. While the job cannot (and should not) be completely automated, you can simplify database requests by using a tool that enables you to pull data from Postgres using Natural Language input. By connecting your database to TalkBI, you can get insights much more easily, simplifying all things product, marketing, and beyond. This is especially useful for lean teams who want their database to work for them instead of the other way around.

Whether you’re a founder, marketer, or product manager, you no longer need to bother developers who will most likely be too busy to prioritize your database requests. So next time you want to check PostgreSQL version, adjust slow queries, or export data for reporting, consider using TalkBI.

Advanced: Setting Up PostgreSQL with Helm Charts

If your team uses Kubernetes, you can simplify the setup of your database. A PostgreSQL Helm chart is a blueprint that tells Kubernetes exactly how to install and configure Postgres for you. It can be compared to a ready-made “recipe” that simplifies the deployment of apps. This way, you can focus on product features instead of spending time on database setup.

By using a Helm chart, you can set up storage, backups, schemas, users, enable SSL with SSL_CIPHERS PostgreSQL, and configure resources for your SaaS.

Example snippet in values.yaml:

1postgresql:
2  postgresPassword: "securepass"
3  service:
4    port: 5432
5  ssl:
6    enabled: true
7    ssl_ciphers: "HIGH:!aNULL:!MD5"

The PostgreSQL ssl_ciphers command verifies that SSL is enabled, secure, and not manually adjusted. After you deploy the above, you can make a quick PostgreSQL version check to make sure the environment is correct. This is important when building a SaaS product, as you prevent manual configuration errors and make deployment faster when looking to test or scale.

PostgreSQL Basics for SaaS Builders and Startup Founders - Master your Database