PostgreSQL备份和还原(多个示例)

lskq00tm  于 2023-06-22  发布在  PostgreSQL
关注(0)|答案(2)|浏览(210)

如果我在一台服务器上运行多个PostgreSQL示例,谁能帮我备份和恢复一个特定的数据库示例?
例如,我在一台服务器上有db1、db2和db3。如何在不影响db2和db3的情况下备份和恢复db1?
下面是我如何分别重新启动示例。

/usr/pgsql-9.6/bin/pg_ctl restart -D /var/lib/pgsql/9.6/db1
 /usr/pgsql-9.6/bin/pg_ctl restart -D /var/lib/pgsql/9.6/db2
 /usr/pgsql-9.6/bin/pg_ctl restart -D /var/lib/pgsql/9.6/db3
b1zrtrql

b1zrtrql1#

谢谢你,@胖弗雷迪。
我能够使用以下命令在具有多个PostgreSQL示例的服务器上备份和恢复特定的数据库示例:
备份:pg_dumpall -p 5435 > /var/lib/pgsql/9.6/db1/PostgreSQL_db1_{date}.sql
恢复:psql -U postgres -p 5435 -f /var/lib/pgsql/9.6/db1/PostgreSQL_db1_{date}.sql

8tntrjer

8tntrjer2#

我在这里留下了一个代码脚本来帮助你。已经很晚了,但至少我可以说它在和相当先进的控制器通信。请随时给予反馈和修改代码。

#!/bin/bash

<<LICENSE
MIT License

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.

LICENSE

# Initial PostgreSQL configuration directory
PG_CONFIG_DIR_INITIAL="/etc/postgresql"

# The directory where the backup will be placed
BACKUP_DIR="/tmp"

# Check if the backup directory exists and is writable
if [ ! -d "${BACKUP_DIR}" ]; then
    echo "The backup directory (${BACKUP_DIR}) does not exist."
    echo "Would you like to create it? (y/n)"
    read create_dir
    if [[ "$create_dir" == "y" ]]; then
      mkdir -p $BACKUP_DIR
    else
      echo "Backup operation cancelled."
      exit 1
    fi
elif [ ! -w "${BACKUP_DIR}" ]; then
    echo "The backup directory (${BACKUP_DIR}) is not writable."
    exit 1
fi

# Dump all PostgreSQL databases, users and their passwords into a SQL commands file
dump_databases() {
    echo "Dumping all databases..."
    if ! sudo -u ${DB_USER} pg_dumpall > "${BACKUP_DIR}/alldb.sql"; then
        echo "Failed to dump databases."
        exit 1
    fi
}

# Backup PostgreSQL configuration files and databases
backup() {
    PG_CONFIG_DIR="${PG_CONFIG_DIR_INITIAL}/${POSTGRES_VERSION}/main"
    BACKUP_FILENAME="postgresql_${POSTGRES_VERSION}_backup_$(date +'%Y%m%d_%H%M%S').tar.gz"
    BACKUP_FILE="${BACKUP_DIR}/${BACKUP_FILENAME}"

    # Check if the PostgreSQL configuration directory exists
    if [ ! -d "${PG_CONFIG_DIR}" ]; then
        echo "The PostgreSQL configuration directory (${PG_CONFIG_DIR}) does not exist."
        exit 1
    fi

    echo "Starting backup for version ${POSTGRES_VERSION}..."

    dump_databases

    # Create a tarball of the configuration directory and databases SQL file
    if ! sudo tar -czvf ${BACKUP_FILE} ${PG_CONFIG_DIR} "${BACKUP_DIR}/alldb.sql"; then
        echo "Failed to create backup."
        exit 1
    fi

    # Verify backup integrity
    echo "Verifying backup integrity..."
    if ! sudo tar -tzf ${BACKUP_FILE} > /dev/null; then
        echo "Backup integrity check failed."
        exit 1
    fi

    echo "Backup created successfully at ${BACKUP_FILE}"
}

# Restore PostgreSQL configuration files and databases
restore() {
    echo "Please enter the full path to the backup file:"
    read BACKUP_FILE

    # Check if the backup file exists
    if [ ! -f "${BACKUP_FILE}" ]; then
        echo "The backup file (${BACKUP_FILE}) does not exist."
        exit 1
    fi

    # Unpack the tarball to the backup directory
    if ! sudo tar -xzvf ${BACKUP_FILE} -C ${BACKUP_DIR}; then
        echo "Failed to extract backup."
        exit 1
    fi

    PG_CONFIG_DIR="${PG_CONFIG_DIR_INITIAL}/${POSTGRES_VERSION}/main"

    # Copy the PostgreSQL configuration files back
    if ! sudo cp -r "${BACKUP_DIR}/main" ${PG_CONFIG_DIR}; then
        echo "Failed to restore configuration files."
        exit 1
    fi

    # Restore all databases
    if ! sudo -u ${DB_USER} psql < "${BACKUP_DIR}/alldb.sql"; then
        echo "Failed to restore databases."
        exit 1
    fi

    # Verify data restoration
    echo "Verifying data restoration..."
    if ! sudo -u ${DB_USER} psql -c "SELECT 1 FROM pg_database LIMIT 1" | grep -q "1"; then
        echo "Data restoration verification failed."
        exit 1
    fi

    # Delete the extracted backup files
    if ! sudo rm -rf "${BACKUP_DIR}/main" "${BACKUP_DIR}/alldb.sql"; then
        echo "Failed to delete extracted backup files."
        exit 1
    fi

    echo "Restore completed successfully for version ${POSTGRES_VERSION}."
}

# Ensure script is run with superuser privileges
if [ "$(id -u)" -ne 0 ]; then
    echo "This script must be run as root"
    exit 1
fi

echo "************************************************************************"
echo "*                      PostgreSQL Backup/Restore                       *"
echo "***********************************************************************"
echo "*                                                                      *"
echo "*  This script allows you to backup and restore PostgreSQL             *"
echo "*  configuration files and databases. Please select an action:         *"
echo "*                                                                      *"
echo "*  1) Backup: Backup PostgreSQL configuration files and databases      *"
echo "*  2) Restore: Restore PostgreSQL configuration files and databases    *"
echo "*                                                                      *"
echo "************************************************************************"

read -p "Choose an action (1 or 2): " ACTION

case $ACTION in
1)
    read -p "Enter PostgreSQL version for backup: " POSTGRES_VERSION
    read -p "Enter PostgreSQL user for backup: " DB_USER
    backup
    ;;
2)
    read -p "Enter PostgreSQL version for restore: " POSTGRES_VERSION
    read -p "Enter PostgreSQL user for restore: " DB_USER
    restore
    ;;
*)
    echo "Invalid action. Exiting."
    exit 1
    ;;
esac

相关问题