In this article, we will set up PostgreSQL database version 17.4 and pgbouncer pooling in the same Google Cloud Engine by using Docker to boot up the container of both services (PostgreSQL and PgBouncer). You can follow the instructions in our article here to get you a Google Cloud Engine and Keypair to SSH to the server. There are several features of improvement in PostgreSQL 17 such as:

1. Partitioning: Enhanced partitioning performance
2. Indexing: Faster Indexing
3. Vacuum: More efficient VACUUM for large scale databases
4. Logical Replication: More robust logical streaming
5. Parallelism: Improved query parallelism
6. JSON/JSONB Support: Advanced SQL/JSON path support
7. Authentication: Security enhancement with encryption
8 Materialized Views: Continued support for real-time analytics
9. Data Security: Improved encryption for sensitive data
10. Performance: Query execution and real-time analytics
11. Machine Learning: Data type support for machine learning models
12. High Availability: Enhanced high-availability support
13. Query Optimization: Query optimizations for analytics

We hope this example can help you to quickly set up PostgreSQL and PgBouncer for development or production purposes.

STEP 1 - INSTALL DOCKER

First, you have to SSH to the Google Cloud Engine, then install Docker using the following link, since our Linux OS is Debian 12.

ssh -i pkeygc.pem setha_corner564@35.192.78.72

Setup Docker's apt repository

# Add Docker's official GPG key:
sudo apt-get update
sudo apt-get install ca-certificates curl
sudo install -m 0755 -d /etc/apt/keyrings
sudo curl -fsSL https://download.docker.com/linux/debian/gpg -o /etc/apt/keyrings/docker.asc
sudo chmod a+r /etc/apt/keyrings/docker.asc

# Add the repository to Apt sources:
echo \
  "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.asc] https://download.docker.com/linux/debian \
  $(. /etc/os-release && echo "$VERSION_CODENAME") stable" | \
  sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt-get update

Install the latest version of Docker packages

sudo apt-get install docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

Finally, verify the installation by running the below command

sudo docker run hello-world

It will show like the picture below if Docker is successfully installed. This success also means Docker Compose can be used since this installation is already covered.

STEP 2 - Create Docker Compose File

sudo mkdir -p /docker/files
cd /docker/files/
sudo vi docker-compose.yml

Next, you have to define the definition of a YAML file for creating the PostgreSQL and PgBouncer container

services:
  db:
    container_name: pg_container
    image: postgres:17.4
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-postgres}
      POSTGRES_HOST_AUTH_METHOD: scram-sha-256
      POSTGRES_INITDB_ARGS: --auth-host=scram-sha-256
    volumes:
      - ./data/pg17:/var/lib/postgresql/data
    ports:
      - '5433:5432'
    networks:
      - dbnet
    restart: unless-stopped
  bnc:
    container_name: bnc_container
    image: bitnami/pgbouncer:1.24.0
    ports:
      - 6432:6432
    environment:
      - POSTGRESQL_HOST=db
      - POSTGRESQL_USERNAME=${POSTGRES_USER:-postgres}
      - POSTGRESQL_PASSWORD=${POSTGRES_PASSWORD:-postgres}
      - PGBOUNCER_AUTH_TYPE=scram-sha-256
      - PGBOUNCER_AUTH_USER=${POSTGRES_USER:-postgres}
      - PGBOUNCER_AUTH_QUERY=SELECT usename, passwd FROM pg_shadow WHERE usename=$1
    volumes:
      - './data/pgbouncer/conf:/bitnami/pgbouncer/conf/'
    networks:
      - dbnet
    restart: unless-stopped
    depends_on:
      - db
networks:
  dbnet:
    driver: bridge

Finally, we use Docker Compose to create containers from the YAML file using the default user and password (postgres / postgres)

sudo docker compose up -d

If the containers were successfully created, you can use the command sudo docker ps -a and you will see the result as below:

STEP 3 - Allow Connection to Google Cloud Engine Through Port PgBouncer 6432

Since we connect to the PostgreSQL database through connection pooling PgBouncer port 6432, we have to create a new firewall rule for our VM instance as the step below:

Finally, we use the Navicat tool to connect to our newly created PostgreSQL 17.4 and PgBouncer as below: