Easy way to save data from an apllication

bonkersdeluxe

Good day,
Is it possible to save easy data from an application and import it.
So i want move one Application from one server to second and not all.
But to prevent all Data in App.
With API Calls it seems very komplex for me.
with pg_dump is it possible ta save and restore one application?

So the Process backup

Export Application
Backup tables from applikation

So the Process Restore

Import the Appliaction of course. So no data are in there only plain Application.
Restore/overwrite the Tables from application.

Thank you!
Best regards Bonkersdeluxe

bonkersdeluxe

So ive installed pgadmin4
That why you say make the application name unique, the Appname is the database name.
I save this Database.

On the other instance i import my app.
After that i only must delete the Database.
The i restore it from file right?
Will this work?
Thank you!
Best regards Bonkersdeluxe

bonkersdeluxe

So i have an solution via docker.
Here my docker conatiner

Requirements:
docker-compose and docker.io installed.
the application version must the same on source and target machine.
If target older please update, if source older please update to.
With application version i mean the Version from your own Build application.

Backup schema with data.

On the new instance, restore application.
Then restore schema. The old schema will automatically deleted.
The container can added in an exists docker-compose file to. the the hast name is the service name from dbserver

Prepare the docker container

install docker.io and docker-compose.
under debian/ubunto apt install docker.io docker-compose
create an directory eg backup_rei
In this directory create an subdirectory backup
insert backup_script.py in root directory from backup_rei
insert Dockerfile in the root directory from backup_rei
instert docker-compose.yml and alter enviroment variables that fits to you.
run backup or restore. The mode is changed as Environment variable in docker-compose.yml file
the commans to run:
if the image was not exist then
docker-compose up -d postgres_backup
if you want rebuild the image to then
docker-compose up -d --build postgres_backup
and the last if you want simply run it again and again
docker-compose up -d postgres_backup
With this command we can show logs
docker-compose logs postgres_backup
In the attachment the files

Our application on source instance with Data

Now make backup with docker conatainer:
root@rei3:~/rei3# docker-compose up -d postgres_backup Recreating rei3_postgres_backup_1 ... done root@rei3:~/rei3# docker-compose logs postgres_backup Attaching to rei3_postgres_backup_1 postgres_backup_1 | Erstelle Backup... postgres_backup_1 | Backup erfolgreich erstellt: /backup/app_20241027_202341.sql root@rei3:~/rei3#

Now an Target machine no applications and

no schema exist

Now install Applikation

No Data in Tab Software, its an virgin installation

Now change docker-composeyml file from backup to restore and edit restore_file variable
- BACKUP_ACTION=restore # Use "backup" for backup and "restore" for restore actions
- RESTORE_FILE=/backup/app_20241027_202341.sql #the path to file to restore
and start the container again
Command with output:
oot@rei3:~/rei3# docker-compose up -d postgres_backup Recreating rei3_postgres_backup_1 ... done root@rei3:~/rei3# docker-compose logs postgres_backup Attaching to rei3_postgres_backup_1 postgres_backup_1 | psql:/backup/app_20241027_202341.sql.temp.sql:1: NOTICE: drop cascades to 6 other objects postgres_backup_1 | DETAIL: drop cascades to table hacker_soft_lizenzmanager.kunden postgres_backup_1 | drop cascades to table hacker_soft_lizenzmanager.lizenzen postgres_backup_1 | drop cascades to table hacker_soft_lizenzmanager.software postgres_backup_1 | drop cascades to sequence hacker_soft_lizenzmanager."sq_23949c20-8f0d-4530-9bf1-35e5db82908b" postgres_backup_1 | drop cascades to sequence hacker_soft_lizenzmanager."sq_524876be-5a68-4162-8a9b-2a240b129d47" postgres_backup_1 | drop cascades to sequence hacker_soft_lizenzmanager."sq_b708e577-d88f-40ef-b594-811bd8aead74" postgres_backup_1 | DROP SCHEMA postgres_backup_1 | SET postgres_backup_1 | SET postgres_backup_1 | SET postgres_backup_1 | SET postgres_backup_1 | SET postgres_backup_1 | SET postgres_backup_1 | set_config postgres_backup_1 | ------------ postgres_backup_1 | postgres_backup_1 | (1 row) postgres_backup_1 | postgres_backup_1 | SET postgres_backup_1 | SET postgres_backup_1 | SET postgres_backup_1 | SET postgres_backup_1 | CREATE SCHEMA postgres_backup_1 | ALTER SCHEMA postgres_backup_1 | CREATE SEQUENCE postgres_backup_1 | ALTER SEQUENCE postgres_backup_1 | SET postgres_backup_1 | SET postgres_backup_1 | CREATE TABLE postgres_backup_1 | ALTER TABLE postgres_backup_1 | CREATE SEQUENCE postgres_backup_1 | ALTER SEQUENCE postgres_backup_1 | CREATE TABLE postgres_backup_1 | ALTER TABLE postgres_backup_1 | CREATE SEQUENCE postgres_backup_1 | ALTER SEQUENCE postgres_backup_1 | CREATE TABLE postgres_backup_1 | ALTER TABLE postgres_backup_1 | COPY 1 postgres_backup_1 | COPY 0 postgres_backup_1 | COPY 2 postgres_backup_1 | setval postgres_backup_1 | -------- postgres_backup_1 | 1 postgres_backup_1 | (1 row) postgres_backup_1 | postgres_backup_1 | setval postgres_backup_1 | -------- postgres_backup_1 | 1 postgres_backup_1 | (1 row) postgres_backup_1 | postgres_backup_1 | setval postgres_backup_1 | -------- postgres_backup_1 | 2 postgres_backup_1 | (1 row) postgres_backup_1 | postgres_backup_1 | ALTER TABLE postgres_backup_1 | ALTER TABLE postgres_backup_1 | ALTER TABLE postgres_backup_1 | CREATE INDEX postgres_backup_1 | CREATE INDEX postgres_backup_1 | ALTER TABLE postgres_backup_1 | ALTER TABLE postgres_backup_1 | Stelle Backup wieder her... postgres_backup_1 | Wiederherstellung erfolgreich. postgres_backup_1 | Temporäre Datei gelöscht: /backup/app_20241027_202341.sql.temp.sql
If we change the Tabs forward and back to Software, now we see our Software Names from Backup.
Voila only one Application restored, all other are untouched.
german umlaute are restored to ;-)

And the schema exists again, of cousre, without this, our application is not working

The Files
backup_script.py

#!/usr/bin/python3
import os
import subprocess
from datetime import datetime

def backup_or_restore():
    # Read environment variables
    host = os.getenv("BACKUP_HOST")
    database = os.getenv("BACKUP_DATABASE")
    schema = os.getenv("BACKUP_SCHEMA")
    user = os.getenv("BACKUP_USER")
    password = os.getenv("BACKUP_PASSWORD")
    action = os.getenv("BACKUP_ACTION", "backup")
    restore_file = os.getenv("RESTORE_FILE")  # Datei für das Restore

    # Set backup file name with current date
    backup_file = f"/backup/{database}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.sql"

    # Set environment variable for password
    env = {"PGPASSWORD": password}

    if action == "backup":
        # Create the backup command
        command = [
            "pg_dump",
            "-h", host,
            "-U", user,
            "-d", database,
            "-n", schema,
            "-f", backup_file
        ]
        try:
            print("Erstelle Backup...")
            subprocess.run(command, env=env, check=True)
            print(f"Backup erfolgreich erstellt: {backup_file}")
        except subprocess.CalledProcessError as e:
            print(f"Fehler beim Erstellen des Backups: {e}")

    elif action == "restore":
        # Verwende die angegebene Datei oder den automatisch benannten Dateinamen
        if not restore_file:
            print("Fehler: Keine Datei für das Restore angegeben.")
            return
        
        drop_schema_command = f"DROP SCHEMA IF EXISTS {schema} CASCADE;\n"
        
        try:
            # Read the specified backup file
            with open(restore_file, 'r') as f:
                backup_content = f.read()

            restore_content = drop_schema_command + backup_content

            # Write to a temporary file
            temp_file = f"{restore_file}.temp.sql"
            with open(temp_file, 'w') as f:
                f.write(restore_content)

            # Run the restore command
            command = [
                "psql",
                "-h", host,
                "-U", user,
                "-d", database,
                "-f", temp_file
            ]
            print("Stelle Backup wieder her...")
            subprocess.run(command, env=env, check=True)
            print("Wiederherstellung erfolgreich.")

            # Delete the temporary file
            os.remove(temp_file)
            print(f"Temporäre Datei gelöscht: {temp_file}")

        except Exception as e:
            print(f"Fehler bei der Wiederherstellung: {e}")
    else:
        print("Ungültige Aktion. Verwenden Sie 'backup' oder 'restore'.")
        if __name__ == "__main__":
            backup_or_restore()

Dockerfile


# Base image: Debian 13
FROM debian:trixie

# Install Python3 and PostgreSQL client tools
RUN apt update && \
    apt install -y python3 python3-pip postgresql-client && \
    rm -rf /var/lib/apt/lists/*

# Set the work directory and backup directory
WORKDIR /backup

# Copy the Python script to /usr/bin
COPY backup_script.py /usr/bin/backup_script.py
RUN chmod +x /usr/bin/backup_script.py

# Set environment variables
ENV BACKUP_HOST ""
ENV BACKUP_DATABASE ""
ENV BACKUP_SCHEMA ""
ENV BACKUP_USER ""
ENV BACKUP_PASSWORD ""
ENV BACKUP_ACTION "backup"  # Default action is "backup", can be "restore" as well

# Run the script on container start
ENTRYPOINT ["/usr/bin/backup_script.py"]

docker-compose.yml File

version: '3.3'

services:    
    postgres_backup:
      build: .
      environment:
        - BACKUP_HOST=127.0.0.1 #hostname or ip
        - BACKUP_DATABASE=app #database name for rei3 let it so
        - BACKUP_SCHEMA=hacker_soft_lizenzmanager #the applikation name
        - BACKUP_USER=app #rei 3 standard user
        - BACKUP_PASSWORD=app #your password (app is standard)
        - BACKUP_ACTION=backup  # Use "backup" for backup and  "restore" for restore actions
        - RESTORE_FILE=/backup/app_20241027_195303.sql #the path to file to restore
      volumes:
        - ./backup:/backup  # Mount local backup folder
      restart: "no"  # Do not restart automatically

Sorry i cant attach txt files.
If you have an idea how i can post the files, so that the spaces are correct.

Best regards Bonkersdeluxe

bonkersdeluxe

Can you mark this Post as resolved, and leave this here for others have the same issue.
thank you!
Best regards Bonkersdeluxe

bonkersdeluxe

PS. Of course if you want to restore on an other instance you must change hostname and other credentials to in the docker-compose.yml.

bonkersdeluxe

PPS. An little improvement.
Add schema in the backup file,
Simply alter this line python script:


    # Set backup file name with current date
    backup_file = f"/backup/{database}_{schema}_{datetime.now().strftime('%Y%m%d_%H%M%S')}.sql"

Then rebuild the image, but before you run, make sure that the mode is backup in docker-compose-file.
Tipp every time you make an restore, change to backup again, so cant forget it next time.
docker-compose up -d --build postgres_backup

So the backup file looks like this:

root@rei3:~/rei3/backup# ls
app_20241027_202341.sql  app_hacker_soft_lizenzmanager_20241027_213631.sql
root@rei3:~/rei3/backup#

And anytime use on your own risk ;-)

gabriel

Sorry, I did not have time to react on Sunday to your questions, but I´m glad you found a good solution for your scenario. Thank you for writing it out and sharing it with the community 🙂