Five Strategies to Get Started with Postgres

Database work typically involves professional database administrators, expensive hardware, and complicated configuration. However, it is possible to launch a proof-of-concept, portable development environment, or low-cost embedded solution while retaining full compatibility with industrial-strength Postgres installations.

Sometimes a test database may be required, but company-maintained databases are off-limits as that violates the organization’s security policies. In commercial situations, databases are installed and maintained by specialized database administrators, and their priorities do not include setting up test databases.

This guide covers different techniques to help create a small, yet fully-capable PostgreSQL database instance in just a few minutes.

As The Economist famously wrote in 2017: “The world’s most valuable resource is … data”. Most organizational data is maintained in a Relational Database Management System (RDBMS). Leading RDBMSs include DB2, MySQL, Oracle, and PostgreSQL, often abbreviated as “Postgres”. DB2 and Oracle are commercially licensed, while MySQL and Postgres are free and open source.

The theory and standards of RDBMS usage are vast. DB2, MySQL, Oracle, and Postgres all adhere to a large percentage of applicable standards, yet all four have small gaps in coverage. However, the similarities between them are far greater than the differences. DB2 and Oracle are generally chosen by organizations that put a premium on commercial service contracts. MySQL is widely used by millions of applications worldwide, and is the first RDBMS of choice for many developers. Conventional thought is that Postgres databases are larger and arguably more capable, or at least fulfill more complicated requirements than corresponding MySQL ones. While MySQL is tuned for data retrieval, Postgres is better at supporting high-performance updates and concurrent write operations.

First, prepare a couple of preliminaries to reuse for each quick-start:

1. Establish a working directory to follow the steps in this guide, for example /home/example-user/postgres:

   mkdir ~/postgres
   Note

   This might have a value such as /Users/example-user/postgres in macOS or C:\Users\example-user\postrges in Windows.

2. Change into your working directory:

   cd ~/postgres

3. Create a small practice script called example.sql:

   nano example.sql

4. Give it the following contents:

   File: example.sql

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   CREATE DATABASE first_database; CREATE TABLE customers ( first_name VARCHAR(20) NOT NULL, last_name VARCHAR(30), age INT ); INSERT INTO customers (first_name, last_name, age) VALUES ('Jane', 'Smith', 44); INSERT INTO customers (first_name, last_name) VALUES ('Juan', 'Fulano'); INSERT INTO customers (first_name, age) VALUES ('Bakti', 23); SELECT * FROM customers ORDER BY last_name;

5. When done, press CTRL+X, followed by Y then Enter to save the file and exit nano.

All of the examples below use this same example.sql script.

PostgreSQL can be run directly on your local machine.

1. First, install PostegreSQL:

   sudo apt install postgresql postgresql-contrib

2. Open a terminal window and navigate to your working directory:

   cd ~/postegre

3. Use the following command to log in as the postegre user:

   sudo -i -u postgres

4. Now enter the Postgres interpreter command line interface:

   psql

5. Enter the following command to run the example.sql script:

   \i example.sql
   

   CREATE DATABASE
      CREATE TABLE
      INSERT 0 1
      INSERT 0 1
      INSERT 0 1
       first_name | last_name | age
      ------------+-----------+-----
       Juan       | Fulano    |
       Jane       | Smith     |  44
       Bakti      |           |  23
      (3 rows)

6. When done, enter the quit command to leave the Postgres interpreter and return to the terminal shell as the postegre user:

   \q

7. Log out as the postegre user:

   exit

1. Open a terminal window and navigate to your working directory:

   cd ~/postegre

2. Use the following command to log in as the postegre user:

   sudo -i -u postgres

3. To connect to a company-wide virtual private network (VPN) and invoke Postgres’s psql command line interface, launch psql as follows:

   psql -h $SERVER -U $YOUR_ACCOUNT

   This approach requires working values for $SERVER, $YOUR_ACCOUNT, and most likely $YOUR_PASSWORD before the connection is established.

4. Once at the psql command prompt, run the following request:

   \i /app/common/example.sql

At this point, data can be added to and retrieved from the remote database.

This approach requires working values for $SERVER, $YOUR_ACCOUNT, and most likely $YOUR_PASSWORD before the connection is established.

When providing a small database instance to use for development experiments, it’s common to act as your own database administrator.

It’s advantageous to know these possibilities even for those who typically never touch a database directly. For example, a developer may be assigned to program Python, TypeScript, or Rust with no explicit database component. A Quality Assurance (QA) specialist may also need a quick “live” database, instead of waiting for someone to set up a mock production one.

For quick results when dealing with an unusual platform, work inside a container. To experiment with a specific version of a particular language, it’s not necessary to learn the peculiarities of that version and that language. Instead, retrieve a standard, tested container that implements that language. With containers, it doesn’t matter whether the desktop is Linux, MacOS, or Windows. There’s no risk of corrupting language installations, of any version. Working inside a Docker-based container guarantees consistent results whatever the host operating system is, and without destabilizing the existing configuration.

Containers are generally a good fit for database management systems, and that includes Postgres. Thanks to the PostgreSQL Docker community, the official Docker image for PostgreSQL is a trustworthy, convenient resource for practical Postgres work. Here’s how to make the most of it:

1. Confirm that the file postgres/example.sql with content mentioned earlier is still present.

2. Install and launch the Docker Engine on your desktop.

3. With the Docker Engine running, launch a standard Postgres container:

   sudo docker run -d --name postgres1 -p 5432:5432 -e POSTGRES_PASSWORD=my_password -v $(pwd)/postgres:/app/common -v /tmp:/var/lib/postgrespostgresql/data postgres

   SOURCE="source=$(pwd)/postgres"
   TARGET=/app/common
   docker run -dit --name postgres1 --rm -it \
              -p 5432:5432 \
              -e POSTGRES_PASSWORD=my_password \
              -e PGDATA=/var/lib/postgresql/data/pgdata \
              -v /tmp:/var/lib/postgresql/data \
              --mount "type=bind,$SOURCE,$TARGET" \
              Postgres
   

   Note

   If your desktop is based on Windows, your launch looks more like:

   SET SOURCE="source=%cd%\postgres"
      …
   

   This outputs a long hash that resembles the following:

   2e1abf0b77caaafe54989ebc51db5c36ed2697c55de98ef4af493

   At this point a Postgres container named postgres is running in the background.

4. Connect to the postgres instance with:

   docker exec -it postgres1 /bin/sh

   You’re again at a command prompt, but it’s the shell interpreter of the Postgres container, rather than your own usual desktop.

5. From the container’s prompt, run:

   psql -U postgres

   The user or account postgres is present by default.

   The psql command line application responds with:

   psql (14.4 (Debian 14.4-1.pgdg110+1))
   Type "help" for help.
   
   postgres=#

   You’re now at the psql prompt, within the container, which itself is running in a terminal of your desktop. Keeping these different contexts straight is essential.

   You can immediately enter such commands as \l or \d which list active databases and active tables, respectively.

6. Even more meaningful, request:

   \i /app/common/example.sql

   and you see output:

   CREATE DATABASE
   CREATE TABLE
   INSERT 0 1
   INSERT 0 1
   INSERT 0 1
    first_name | last_name | age
   ------------+-----------+-----
    Juan       | Fulano    |
    Jane       | Smith     |  44
    Bakti      |           |  23
   (3 rows)

as in the earlier remote example.

Now you have data in a table of a database container running on your desktop. You have all Postgres’ capabilities “containerized” conveniently, and can run your own queries, and updates.

One of the advantages of working inside a container is that you can launch multiple containers on your desktop. If you have long-running experiments to run, or multiple distinct projects, configuration of several different containers is a handy way to keep the different databases from interfering with each other.

Another entry point for Postgres work is to create a dedicated virtual machine, and assign it database duties. This is similar to the first example in that the desktop connects to a local database server instead of a remote database server.

Linode supports several operating systems, and provides instructions for standard installations of Postgres on Ubuntu 20.04 LTS, CentOS 8, and several others. With the proper know-how, re-purposing an existing physical machine could also be a cost effective solution.

Another variation is to set up a local testing database server on a Raspberry Pi or similar tiny PC. A capable Linux server with a few dozen gigabytes of mass storage can be purchased for well under $100. Postgres is available through the Raspberry Pi OS package repository, and a standard local system install is well-documented.

That’s not to say that quick setups are unique to Raspberry Pi. Most Debian-derived Linux distributions only require a single installation command:

apt install postgresql

A small amount of networking configuration and user management can transform many low-end hardware configurations into database servers. Once that first psql connection is installed, all of Postgres’ capabilities are at hand.

Postgres is highly portable and easy to set up. Many developers only require small database instances with just a few gigabytes of storage. This requires considerably less infrastructure than a production database that manages many terabytes of data. Because Postgres is consistent across environments, developers can work in their own development-specific instances and simply concentrate on the functions and features. Whatever the circumstances, there’s likely an efficient way create a low-cost database server to support programming work.

More Information

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

• Link Title 1
• Link Title 2