Migrating a postgresQL DB from Docker on Localhost to Docker VPS: Command-by-Command Cheat Sheet

This summary is a personal note on solving the problem specified in the title. The goal of the problem: to share a local database for simultaneous access by several clients. A cheap VPS from a well-known hoster.

Introductory

It is assumed that the local host already has Docker and the container with the required version is launched PostgresQLwhere the database is initialized, which is the target for the transfer. Synchronization of the local and remote databases is not provided in the future.

Just in case, I provide the key parameters of my system at the time of the work:

Let's look at running containers:

docker ps

Launch the terminal inside the desired container:

docker exec -it myPostgreSQL_1 bash 

Create a dump of the required database (substitute your name instead beads):

 pg_dump -U postgres beads > /tmp/beads.sql 

Now your database is saved in the folder inside the container on the local host /tmp/

Don't forget to come back from bash'a docker:

 exit 

Copy the resulting dump from the container to the local host in the / foldertmp/:

 docker cp myPostgreSQL_1:/tmp/beads.sql /tmp/ 

Now you can see your dump in the specified folder on the local host.

Receiving VPS

Surely, RUVDS – not the best option, but at this stage this service suits me for training. I chose them cheap car the simplest option, which always costs about 100 rubles due to the endless promotion.

so that it doesn’t look like a promotion of the service, I’ll hide it here:

Click “VPS Start”:

Next you can choose the physical location of the server (probably so). Just in case, I will say that I chose “Moscow M9“:

There is a choice of OS. Next I use Debian:

In your personal account we receive IP to access an empty machine:

I'll duplicate what the command outputs hostnamectl on the received server:

Working with VPS

The server comes pre-installed SSH. That's why further communication with the server will take place via this protocol. We connect:

ssh <имя пользователя>@<ip Вашего VPS>

“Out of the box” you need to connect with a name root and the password that is given to you in your personal account, but for some security reasons you need to add a new user and completely disable the ability to log in with the root name. However, this can be sorted out when is yours VPS let's start mining later.

After connecting, you can see what else is preinstalled:

dpkg-query -l

We create a folder on the server for temporary transfer of the previously prepared dump:

mkdir tmp

It turns out something like /home/roman/tmp.

Let's go to the local host terminal.

We transfer the dump from the local host to the server:

 scp /tmp/beads.sql roman@194.87.93.164:/home/roman/tmp

Let's go to the terminal VPS.

Install Docker on the server (I did it according to this tutorial):

sudo apt update -y
sudo apt upgrade -y
sudo apt install -y docker.io
sudo systemctl start docker

We pull up and launch an empty container with PostgresQL:

sudo docker pull postgres:16
docker run -d \
-p 5432:5432 \
--name myPostgreSQL \
-e POSTGRES_USER=<ВАШЕ ИМЯ ПОЛЬЗОВАТЕЛЯ> \
-e POSTGRES_PASSWORD=<ВАШ ПАРОЛЬ ОТ ЭТОГО ПОЛЬЗОВАТЕЛЯ> \
postgres:16

We transfer the DB dump inside the container on the server:

sudo docker cp ~/tmp/beads.sql myPostgreSQL:/tmp/

Here myPostgreSQL – this is my container name. The specific name of the running container can always be checked with the command above docker ps.

Launch psql inside the container:

 sudo docker exec -it myPostgreSQL psql -U postgres

Inside psql import DB:

 \i /tmp/beads.sql

Since we have 10 GB of ROM on the server, we can also install pgadmin directly on the server:

sudo docker pull dpage/pgadmin4:7.7
docker run -d\
 -p 5050:80 \
 --name myPGAdmin \
 -e "PGADMIN_DEFAULT_EMAIL=<ВАШ email>" \
 -e "PGADMIN_DEFAULT_PASSWORD=<ВАШ ПАРОЛЬ>" \
 dpage/pgadmin4:7.7

Now you can add a server from any pgadmin with internet access:

If everything is ok, then in pgadmin You can already view the database.

Code on python also connects without any hitches:

import psycopg2
from psycopg2 import Error
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT

import configparser
config = configparser.ConfigParser()
config.read("config.ini")

class DB:
    def __init__(self):           
        try:
            # Подключение к существующей базе данных
            self.conn = psycopg2.connect(dbname = config['beads_db']['dbname'],
                                        user = config['beads_db']['user'],
                                        password = config['beads_db']['password'],
                                        host = config['beads_db']['host'],
                                        port = config['beads_db']['port'])
            print("соединение установлено")
            self.conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
            # Курсор для выполнения операций с базой данных
            self.cur = self.conn.cursor()
            print("курсор создан")

        except (Exception, Error) as error:
            print("Ошибка при работе с PostgreSQL", error)