Introduction

Recently, I set up an RSS service using Miniflux with PostgreSQL as its database, running inside a Docker container. Now I need to migrate the database to PostgreSQL on another server, and configure Miniflux to connect to the migrated database using a Database URL.

Detailed Steps

1. Enter the container using the docker exec command

   BASH

   # List all containers
   docker ps -a
   # Enter the PostgreSQL container
   docker exec -it 68b4 /bin/bash
   # Note: 68b4 is the first few characters of the container ID
   

2. Back up the database using pg_dump

   BASH

   # Back up the database
   pg_dump --username=admin \
   --host=0.0.0.0 --port=5432 \
   --format=plain --file=/var/lib/postgresql/data/backup.sql db_name
   # Note: admin is the database username, db_name is the database name
   
   

View the backup file

cat /var/lib/postgresql/data/backup.sql

Exit the container

exit

3. Copy the backup file to the host machine

bash docker cp 68b4cbec5817:/var/lib/postgresql/data/backup.sql ~/

Note: 68b4cbec5817 is the container ID, ~/ is the current user's home directory on the host

4. Create the database on the new server

This assumes PostgreSQL is already installed. For a tutorial, refer to [How To Install and Use PostgreSQL on Ubuntu 24.04](https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-24-04)

bash

Switch to the postgres user

sudo su postgres

Enter the postgres CLI

psql

Create a user

postgres=# CREATE USER username WITH PASSWORD '*';

Here, postgres=# is the PostgreSQL prompt, do not type it

username is the database username, * is the database password

Create a database

postgres=# CREATE DATABASE dbname OWNER dbuser;

dbname is the database name, dbuser is the username just created

Grant privileges

postgres=# GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;

dbname is the database name, dbuser is the username just created

List databases

postgres=# \l

List users

postgres=# \du

Exit the postgres CLI

postgres=# \q

Exit the postgres user

exit

5. Copy the backup file to the new server

bash scp ~/backup.sql username@ip:/home/username/

Note: username is the username on the new server, ip is the IP address of the new server, backup.sql is the backup file name

6. Import the database into PostgreSQL on the new server

bash

Switch to the postgres user

sudo su postgres

Import the database

psql --host=localhost --port=5432 \ --username=miniflux --dbname=minifluxdb \ --file=/home/unbuntu/backup.sql

Note: miniflux is the database username, minifluxdb is the database name, backup.sql is the backup file name

7. Configure PostgreSQL on the new server

bash

Switch to the postgres user

sudo su postgres

Enter the postgres CLI

psql

Show the hba_file path

SHOW hba_file;

Example output

/etc/postgresql/14/main/pg_hba.conf

Exit the postgres CLI

\q

Edit the hba_file

vim /etc/postgresql/14/main/pg_hba.conf

Modify the listening port

host all all 0.0.0.0/0 md5 host all all ::/0 md5

Save and exit

ESC + :wq

Edit the postgresql.conf file

vim /etc/postgresql/14/main/postgresql.conf

Modify the listen address

Change listenaddress = 'localhost' To listenaddress = '*'

Save and exit

ESC + :wq

Restart the PostgreSQL service

systemctl restart postgresql

Exit the postgres user

exit

8. Modify Miniflux's Database URL
In the Miniflux compose file, update the database URL

yaml

Database URL Schema

postgres://DatabaseUserName:DatabasePassword@HostIP:5432/DatabaseName?sslmode=disable

9. Restart the Miniflux service

bash docker compose up -d