pg复制远端数据库

https://sqlbackupandftp.com/blog/how-to-backup-and-restore-postgresql-database

1 备份

pg_dump -v -f gscloud1912 -C -h 10.24.12.143 -U gscloud1912 gscloud1912

2 还原

创建数据库角色

创建数据库

3还原

psql -v -h localhost -d gscloud1912 -U gscloud1912 -f gscloud1912

windows

psql -v b5 -U b5 -f b5

4 如果网速快且数据量小可以执行一下命令

pg_dump -C -h local -U localuser sourcedb | psql -h remote -U remoteuser targetdb

5 本地复制

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

6 从文件还原

#!/bin/bash

if [ 1 -ne $# ]
then
  echo "Usage `basename $0` {tar.gz database file}"
  exit 65;
fi

if [ -f "$1" ]
then
  EXTRACTED=`tar -xzvf $1`
  echo "using database archive: $EXTRACTED";
else
  echo "file $1 does not exist"
  exit 1
fi


PGUSER=dbuser
PGPASSWORD=dbpw
export PGUSER PGPASSWORD

datestr=`date +%Y%m%d`


dbname="dbcpy_$datestr"
createdbcmd="CREATE DATABASE $dbname WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;"
dropdbcmp="DROP DATABASE $dbname"

echo "creating database $dbname"
psql -c "$createdbcmd"

rc=$?
if [[ $rc != 0 ]] ; then
  rm -rf "$EXTRACTED"
  echo "error occured while creating database $dbname ($rc)"
  exit $rc
fi


echo "loading data into database"
psql $dbname < $EXTRACTED > /dev/null

rc=$?

rm -rf "$EXTRACTED"

if [[ $rc != 0 ]] ; then
  psql -c "$dropdbcmd"
  echo "error occured while loading data to database $dbname ($rc)"
  exit $rc
fi


echo "finished OK"