Docker runs PostgreSQL database maintenance and executes scripts to back up the database and update the table structure

Introduction to PostgreSQL

PostgreSQL is an open source relational database management system, a powerful, highly customizable and complex application-enabled database. It supports a wide range of data types, including numerical, text, binary, geospatial and other types, and also has advanced data modeling and table connection functions. PostgreSQL is a database that uses the ANSI SQL standard and also supports various data formats such as JSON and XML.
PostgreSQL is a powerful, reliable and rich database management system, widely used in large enterprises and various applications.
In the open source world, PostgreSQL is widely recognized and supported, and has a large community and developer team around the world. It is a mature database technology and one of the most dynamic and development potential open source databases today.

Business scenarios

We have a small number of customers, whose projects involve fewer intersections, they have less demand for the platform, and they have no requirements for the concurrency of the platform.
On the other hand, it is also a relatively important reason. They do not have the budget to buy a new server, but will only provide us with an older Windows server server to deploy the platform.
For the convenience of deployment and operation and maintenance, we will deploy all microservices and middleware using docker and use docker-compose to manage configuration files uniformly
Install virtualBox on Windows machines, install CentOS7 virtual machine, and deploy the platform in the virtual machine (the database is also deployed using docker)
The customer is in the intranet environment. When the subsequent updates are deployed, the main thing is to organize the deployment package and update scripts, and local maintenance and cooperation with the update. A problem arises at this time. Sometimes the table structure needs to be modified, and the local maintenance personnel do not know how to operate (in fact, they mainly use the platform to help customers work, not professional operation and maintenance)
Now we need to put the database structure update process in the original program update script, so that maintenance personnel only need to execute one script to complete the update
For a production environment, a database backup strategy is necessary, and it must be backed up at least once a day. Since we only store business data in PostgreSQL, the overall data volume is not large, so we use full backup

Database maintenance

docker-compose configuration

First, let’s put a look at our PostgreSQL docker-compose configuration

  # Database Service  postgresql:
    image: postgres:14.2
    container_name: postgres
    ports:
      - 5432:5432
    volumes:
      - ./volumes/postgresql/:/var/lib/postgresql/data/
      - /etc/localtime:/etc/localtime
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=xxxxxxxx
      - POSTGRES_HOST_AUTH_METHOD=md5
      - TZ=Asia/Shanghai
    restart: always

The main thing is to map the data directory where PostgreSQL database stores data. The others are all common configurations, such as username, password, password access, time zone, etc.
The data directory is mapped, which is convenient for full backup and recovery of data.

Backup script

You can use timed tasks to directly compress and backup the mapped folders to other directories in the early morning of every day.
Below is the backup script, which only retains 10 days of backup, and loops to delete the earliest day

#!/bin/bash
echo "======== start backup pg data ========"
# pg data directorypgdata=/home/signal/instance/volumes
target=/home/bak/pgbak
if [ ! -d $target ];then
    mkdir -p $target
else
    echo "The folder already exists"
fi
# Current datecurDay=$(date "+%Y-%m-%d")
echo ${curDay}
# Number of files reservedReservedNum=10
# Go to the pg data directory and compress it to the backup folder by datecd $pgdata
tar -zcvf $target/postgresql-$ postgresql
# Delete compressed files with extra days#*.tgz means filtering file types, and removing files for the entire directorycd $target
FileNum=$(ls -l  ./*.tgz |grep ^- |wc -l)
while(( FileNum &gt; $ReservedNum))
do
	OldFile=$(ls -rt  ./*.tgz| head -1)
	rm -f $OldFile
	let "FileNum--"
done
echo "======== bakup pg data end ========"

Then add the script to the server and execute it regularly, you can use it directlycrontab

Update table structure script

Docker can execute sh scripts, usedocker exec container name/id /bin/sh -c "Command"Just
In the original program update script, add the database update command. The specific commands are as follows:

echo "Update postgresql table structure"
docker exec postgres /bin/sh -c "sh /var/lib/postgresql/data/db_update.sh"

in/var/lib/postgresql/data/db_update.shIt is a database update script, usingpsql The command executes the SQL file, as follows:

#!/bin/bash
# Execute this script as postgres user Update the databaseecho 'update database'
psql -d core -U postgres -f /var/lib/postgresql/data/core_update.sql
sleep 1s
echo 'create update end'
exit

in/var/lib/postgresql/data/core_update.sqlFor specific SQL files
because/var/lib/postgresql/dataThe folder has been mapped. When updating the deployment program, update it by the waycore_update.sqlJust

This is the article about docker running PostgreSQL database maintenance and execution script backup database and updating table structure. For more related content of docker running PostgreSQL database, please search for my previous articles or continue browsing the following related articles. I hope everyone will support me in the future!