Installing and Configuring PostgreSQL on Linode

PostgreSQL, often simply “Postgres,” is a powerful, open-source object-relational database system with over 30 years of active development. Known for its reliability, feature robustness, and performance, PostgreSQL is an excellent choice for various applications, from small projects to large-scale enterprise solutions. This guide will walk you through the process of installing and configuring PostgreSQL on your [Linode server][].

This guide assumes you have already set up and secured your Linode server. If you haven’t done so, please refer to our separate article “Setting Up and Securing Your Linode Server” before proceeding with this PostgreSQL installation guide.

1. Installing PostgreSQL

The first step in our PostgreSQL journey is installation. While many Linux distributions include PostgreSQL in their default repositories, it’s often beneficial to use the official PostgreSQL repository to ensure you have access to the latest version and features.

First, we need to update the apt package manager and related certificate tools:

sudo apt-get update
sudo apt-get install -y apt-transport-https ca-certificates curl gnupg

Download the public signing key for the official PostgreSQL package repository:

# If the folder `/etc/apt/keyrings` does not exist, it should be created first:
# sudo mkdir -p -m 755 /etc/apt/keyrings
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/keyrings/postgres.gpg
sudo chmod 644 /etc/apt/keyrings/postgres.gpg

This downloads and stores the repository signing key to your system.

To add the official PostgreSQL repository, run the following command:¹

sudo sh -c 'echo "deb [arch=amd64 signed-by=/etc/apt/keyrings/postgres.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

The command lsb_release -c is used to determine the codename of your Ubuntu distribution (e.g., noble for Ubuntu 24.04 LTS, codename Noble Numbat).

Alternatively, you can add the repository directly to your /etc/apt/sources.list file like so:

deb [arch=amd64 signed-by=/etc/apt/keyrings/postgres.gpg] http://apt.postgresql.org/pub/repos/apt noble-pgdg main

Using the official repository ensures you’ll have access to the latest PostgreSQL updates and security patches as soon as they’re available.

Next, update your package lists and install PostgreSQL along with some additional functionality:

sudo apt update
sudo apt install postgresql postgresql-contrib

The postgresql-contrib package provides additional utilities and functionality that many PostgreSQL installations find useful, such as additional data types, functions, and other extensions.

After installation, it’s a good idea to verify that PostgreSQL was installed correctly. You can do this by checking the version and service status:

psql --version
sudo systemctl status postgresql

You should see output indicating the installed PostgreSQL version and that the service is active and running.

Finally, to ensure PostgreSQL starts automatically when your system boots, enable it with systemctl:

sudo systemctl enable postgresql

This step is crucial for production environments where you want your database to be available immediately after a system restart.

2. Configuring PostgreSQL

With PostgreSQL installed, it’s time to configure it for use. We’ll start by accessing the PostgreSQL prompt as the default postgres user:

sudo -i -u postgres
psql

Once at the PostgreSQL prompt, let’s create a new database user. It’s a best practice to create specific users for each application or purpose, rather than using the default postgres superuser:

CREATE USER yourusername WITH PASSWORD 'yourpassword';

Replace ‘yourusername’ and ‘yourpassword’ with your desired credentials. When creating users, consider implementing a strong password policy, such as requiring a minimum length and a mix of character types.

Next, create a new database:

CREATE DATABASE yourdbname;

You can create multiple databases for different applications or purposes. PostgreSQL allows for efficient management of multiple databases within a single PostgreSQL instance.

After creating the database, grant the necessary privileges to your new user:

GRANT ALL PRIVILEGES ON DATABASE yourdbname TO yourusername;
ALTER DATABASE yourdbname OWNER TO yourusername;

This grants full access to the specified database. In a production environment, you might want to be more selective with privileges, granting only those necessary for the user’s intended tasks.

Exit the PostgreSQL prompt by typing \q and type exit to exit the shell of the postgres user.

For more granular control over client authentication, you may want to modify the pg_hba.conf file (in this instance, for PostgreSQL version 16):

sudo nano /etc/postgresql/16/main/pg_hba.conf

This is the PostgreSQL Host-Based Authentication configuration file, which controls how PostgreSQL authenticates client connections. It’s one of the primary security mechanisms in PostgreSQL.

In the pg_hba.conf file, you’ll find lines that look something like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256

You might want to change the authentication method from ‘peer’ to ‘md5’ for local connections, which allows password-based authentication for local connections. Furthermore, passwords are transmitted in MD5-hashed form for security and the PostgreSQL username doesn’t need to match the operating system username. It should look like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     md5

After making the change, restart PostgreSQL to apply it:

sudo systemctl restart postgresql

If you encounter any issues during restart, check the PostgreSQL log files for error messages that can help with troubleshooting.

3. Securing Your PostgreSQL Installation

Security is paramount when dealing with databases. Here are some key steps to secure your PostgreSQL installation:

Use strong passwords for all database users. Consider implementing a password policy that requires a minimum length, a mix of upper and lowercase letters, numbers, and special characters.

By default, PostgreSQL only listens on localhost. If you need remote access, you’ll need to modify the postgresql.conf file and configure your firewall carefully. Always use the principle of least privilege, only opening access where absolutely necessary.

Enabling SSL connections is crucial for encrypting data in transit. To set up SSL, you’ll need to generate SSL certificates and configure PostgreSQL to use them. Modify the postgresql.conf file to set ssl = on and specify the paths to your SSL certificate and key:

sudo nano /etc/postgresql/16/main/postgresql.conf

Note that some installations may already have SSL enabled with an automatically generated “snakeoil” certificate. The “snakeoil” certificate should only be used for testing or in controlled environments where trust isn’t a concern. It should be replaced with a properly signed certificate from a trusted Certificate Authority (CA) for production.

Keeping your PostgreSQL installation up-to-date is critical for security. Regularly check for and apply updates, especially security patches. Consider setting up automatic updates or a regular update schedule.

4. Basic PostgreSQL Operations

Now that your PostgreSQL instance is set up and secured, let’s explore some basic operations.

To connect to a database, use the following command:

psql -d yourdbname -U yourusername

You’ll be prompted for your password. Once connected, you can start interacting with your database.

Creating tables is a fundamental operation. Here’s an example of creating a more complex table:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This creates a table with various column types, including a serial ID, varchar fields, a text field for longer content, a decimal for price, and timestamps for tracking creation and update times.

To insert data into your table:

INSERT INTO products (name, description, price, category)
VALUES ('Widget', 'A fantastic widget', 19.99, 'Gadgets');

For bulk inserts, you can use the COPY command or prepare a batch INSERT statement for better performance.

Querying data is where PostgreSQL’s power really shines. Here’s a more advanced query example:

SELECT category, COUNT(*) as product_count, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;

This query groups products by category, counts the number of products and calculates the average price for each category, only showing categories with more than 5 products, sorted by average price in descending order.

When updating data, it’s often a good idea to use transactions for data integrity:

BEGIN;
UPDATE products SET price = price * 1.1 WHERE category = 'Gadgets';
UPDATE products SET updated_at = CURRENT_TIMESTAMP WHERE category = 'Gadgets';
COMMIT;

This updates the price of all ‘Gadgets’ by 10% and updates the ‘updated_at’ timestamp, all within a single transaction.

For deleting data, be cautious and use WHERE clauses to avoid accidental deletions:

DELETE FROM products WHERE created_at < NOW() - INTERVAL '1 year';

This deletes all products created more than a year ago. Always double-check your DELETE statements before executing them.

5. Routine Maintenance

Maintaining a PostgreSQL database involves regular monitoring and adjustments to ensure optimal performance. Here are some key practices:

• Regularly use the VACUUM command to reclaim storage and prevent transaction ID wraparound issues.

• Implement a robust backup strategy to prevent data loss. PostgreSQL offers several backup methods, including SQL dumps and continuous archiving. Tools like pg_dump and pg_basebackup can be used for this purpose. Always test your backup and recovery procedures to ensure they work when needed.

• Utilize pg_stat_statements and other monitoring tools available within PostgreSQL to analyze and optimize query performance.

6. Advanced PostgreSQL Features

PostgreSQL offers many advanced features that set it apart from other database systems. Here’s a brief introduction to some of these features:

• Full-text search capabilities allow for natural language searching of text content. You can create special indexes to speed up these searches and even rank results by relevance.

• JSON and JSONB support enables storing and querying JSON data directly in PostgreSQL. This is particularly useful for applications that need to store semi-structured data alongside traditional relational data.

• PostgreSQL offers various indexing strategies for performance optimization. Beyond standard B-tree indexes, you can use GiST, GIN, and BRIN indexes for different types of data and query patterns.

• For very large tables, PostgreSQL supports table partitioning. This can significantly improve query performance and make managing large datasets easier.

• PostgreSQL’s extension ecosystem allows you to add new functionality to your database. Popular extensions include PostGIS for geographic data and pgcrypto for additional cryptographic functions.

Conclusion

You’ve now successfully installed, configured, and started using PostgreSQL on your Linode server. This powerful database system is ready to support your applications and data management needs. Remember, what we’ve covered here is just the beginning—PostgreSQL offers a wealth of advanced features that you can explore further as your needs grow.

As you continue your PostgreSQL journey, make sure to refer to the official documentation for in-depth information on features, best practices, and advanced configurations. Don’t hesitate to explore PostgreSQL’s more advanced features as your projects evolve.

Additional Resources

• PostgreSQL Official Documentation
• PostgreSQL Tutorial
• PostgreSQL Exercises
• Planet PostgreSQL
• PostgreSQL Wiki

Remember, the PostgreSQL community is vast and supportive. Don’t hesitate to seek help on forums or mailing lists if you encounter any issues or have questions as you work with PostgreSQL.