PostgreSQL에서 데이터베이스 복사본 생성

PostgreSQL에서 데이터베이스 복사본 생성

---

전체 데이터베이스 (구조 및 데이터)를 pgAdmin의 새 데이터베이스에 복사하는 올바른 방법은 무엇입니까?

---

Postgres를 사용하면 새 데이터베이스를 만들 때 서버의 기존 데이터베이스를 템플릿으로 사용할 수 있습니다. pgAdmin이 데이터베이스 만들기 대화 상자에서 옵션을 제공하는지 확실하지 않지만 그렇지 않은 경우 쿼리 창에서 다음을 실행할 수 있어야합니다.

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

그래도 다음을 얻을 수 있습니다.

ERROR:  source database "originaldb" is being accessed by other users

이를 수정하려면이 쿼리를 사용할 수 있습니다.

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();

---

Bell의 대답 의 명령 줄 버전 :

createdb -O ownername -T originaldb newdb

일반적으로 postgres와 같은 데이터베이스 마스터의 권한으로 실행해야합니다.

---

postgres로 기존 데이터베이스를 복제하려면 그렇게 할 수 있습니다.

/* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid <> pg_backend_pid();

/* CLONE DATABASE TO NEW ONE(TARGET_DB) */
CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;

그것은 오류를 피하기 위해 소스 DB에 대한 모든 연결을 종료합니다.

ERROR:  source database "SOURCE_DB" is being accessed by other users

---

원래 데이터베이스가 트래픽을받는 프로덕션 환경에서는 다음을 사용하고 있습니다.

pg_dump production-db | psql test-db

---

pgAdmin에 대해 모르지만 pgdumpSQL로 데이터베이스 덤프를 제공합니다. 동일한 이름으로 데이터베이스를 생성하고

psql mydatabase < my dump

모든 테이블과 해당 데이터 및 모든 액세스 권한을 복원합니다.

---

먼저 sudo데이터베이스 사용자로서 :

sudo su postgres

PostgreSQL 명령 줄로 이동합니다.

psql

새 데이터베이스를 만들고 권한을 부여한 다음 종료합니다.

CREATE DATABASE new_database_name;
GRANT ALL PRIVILEGES ON DATABASE new_database_name TO my_user;
\d

이전 데이터베이스의 구조와 데이터를 새 데이터베이스로 복사합니다.

pg_dump old_database_name | psql new_database_name

---

위의 예제와 함께이 접근 방식을 구성했습니다. "부하 상태"서버에서 작업 중이며 @zbyszek에서 접근을 시도 할 때 오류가 발생했습니다. 나는 또한 "명령 줄 전용"솔루션을 추구했습니다.

createdb: database creation failed: ERROR: source database "exampledb" is being accessed by other users.

다음은 나를 위해 일한 것입니다 ( 출력을 파일로 이동하고 서버 연결 끊김으로부터 보호하기 위해 앞에 추가 된 명령nohup ) :

1. nohup pg_dump exampledb > example-01.sql
2. createdb -O postgres exampledbclone_01

   내 사용자는 "postgres"입니다.

3. nohup psql exampledbclone_01 < example-01.sql

---

In pgAdmin you can make a backup from your original database, and then just create a new database and restore from the backup just created:

1. Right click the source database, Backup... and dump to a file.
2. Right click, New Object, New Database... and name the destination.
3. Right click the new database, Restore... and select your file.

---

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?

Answer:

CREATE DATABASE newdb WITH TEMPLATE originaldb;

Tried and tested.

---

PostgreSQL 9.1.2:

$ CREATEDB new_db_name -T orig_db_name -O db_user;

---

For those still interested, I have come up with a bash script that does (more or less) what the author wanted. I had to make a daily business database copy on a production system, this script seems to do the trick. Remember to change the database name/user/pw values.

#!/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"

---

To create database dump

cd /var/lib/pgsql/
pg_dump database_name> database_name.out

To resote database dump

psql -d template1
CREATE DATABASE database_name WITH  ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-8' TEMPLATE template0;
CREATE USER  role_name WITH PASSWORD 'password';
ALTER DATABASE database_name OWNER TO role_name;
ALTER USER role_name CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE database_name to role_name;


CTR+D(logout from pgsql console)
cd /var/lib/pgsql/

psql -d database_name -f database_name.out

---

From the documentation, using createdb or CREATE DATABASE with templates is not encouraged:

Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose “COPY DATABASE” facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes.

pg_dump or pg_dumpall is a good way to go for copying database AND ALL THE DATA. If you are using a GUI like pgAdmin, these commands are called behind the scenes when you execute a backup command. Copying to a new database is done in two phases: Backup and Restore

pg_dumpall saves all of the databases on the PostgreSQL cluster. The disadvantage to this approach is that you end up with a potentially very large text file full of SQL required to create the database and populate the data. The advantage of this approach is that you get all of the roles (permissions) for the cluster for free. To dump all databases do this from the superuser account

pg_dumpall > db.out

and to restore

psql -f db.out postgres

pg_dump has some compression options that give you much smaller files. I have a production database I backup twice a day with a cron job using

pg_dump --create --format=custom --compress=5 ==file=db.dump mydatabase

where compress is the compression level (0 to 9) and create tells pg_dump to add commands to create the database. Restore (or move to new cluster) by using

pg_restore -d newdb db.dump

where newdb is the name of the database you want to use.

Other things to think about

PostgreSQL uses ROLES for managing permissions. These are not copied by pg_dump. Also, we have not dealt with the settings in postgresql.conf and pg_hba.conf (if you're moving the database to another server). You'll have to figure out the conf settings on your own. But there is a trick I just discovered for backing up roles. Roles are managed at the cluster level and you can ask pg_dumpall to backup just the roles with the --roles-only command line switch.

---

If the database has open connections, this script may help. I use this to create a test database from a backup of the live-production database every night. This assumes that you have an .SQL backup file from the production db (I do this within webmin).

#!/bin/sh

dbname="desired_db_name_of_test_enviroment"
username="user_name"
fname="/path to /ExistingBackupFileOfLive.sql"

dropdbcmp="DROP DATABASE $dbname"
createdbcmd="CREATE DATABASE $dbname WITH OWNER = $username "

export PGPASSWORD=MyPassword



echo "**********"
echo "** Dropping $dbname"
psql -d postgres -h localhost -U "$username" -c "$dropdbcmp"

echo "**********"
echo "** Creating database $dbname"
psql -d postgres -h localhost -U "$username" -c "$createdbcmd"

echo "**********"
echo "** Loading data into database"
psql -d postgres -h localhost -U "$username" -d "$dbname" -a -f "$fname"

---

Using pgAdmin, disconnect the database that you want to use as a template. Then you select it as the template to create the new database, this avoids getting the already in use error.

---

If you want to copy whole schema you can make a pg_dump with following command:

pg_dump -h database.host.com -d database_name -n schema_name -U database_user --password

And when you want to import that dump, you can use:

psql "host=database.host.com user=database_user password=database_password dbname=database_name options=--search_path=schema_name" -f sql_dump_to_import.sql

More info about connection strings: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

Or then just combining it in one liner:

pg_dump -h database.host.com -d postgres -n schema_name -U database_user --password | psql "host=database.host.com user=database_user password=database_password dbname=database_name options=--search_path=schema_name”

---

1. Open the Main Window in pgAdmin and then open another Query Tools Window
2. In the main windows in pgAdmin,

Disconnect the "templated" database that you want to use as a template.

3. Goto the Query Tools Window

Run 2 queries as below

SELECT pg_terminate_backend(pg_stat_activity.pid) 
    FROM pg_stat_activity 
    WHERE pg_stat_activity.datname = 'TemplateDB' AND pid <> pg_backend_pid(); 

(The above SQL statement will terminate all active sessions with TemplateDB and then you can now select it as the template to create the new TargetDB database, this avoids getting the already in use error.)

CREATE DATABASE 'TargetDB'
  WITH TEMPLATE='TemplateDB'
       CONNECTION LIMIT=-1;

---

Try this:

CREATE DATABASE newdb WITH ENCODING='UTF8' OWNER=owner TEMPLATE=templatedb LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' CONNECTION LIMIT=-1;

gl XD

참고URL : https://stackoverflow.com/questions/876522/creating-a-copy-of-a-database-in-postgresql