Postgre에서 데이터베이스 복사본 작성SQL

Postgre에서 데이터베이스 복사본 작성SQL

전체 데이터베이스(구조 및 데이터)를 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)의 권한으로 실행해야 합니다.

포스트그레스를 사용하여 기존 데이터베이스를 복제하려면 다음 작업을 수행합니다.

/* 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;

IT는 오류를 방지하기 위해 소스 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

포스트그레로 이동SQL 명령줄:

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

pgAdmin에서는 원래 데이터베이스에서 백업을 만든 다음 새 데이터베이스를 만들고 방금 만든 백업에서 복원할 수 있습니다.

1. 원본 데이터베이스, 백업...을 마우스 오른쪽 버튼으로 클릭합니다.파일에 덤프합니다.
2. 마우스 오른쪽 단추 클릭, 새 개체, 새 데이터베이스...목적지 이름을 지정합니다.
3. 새 데이터베이스, 복원...을 마우스 오른쪽 버튼으로 클릭합니다.파일을 선택합니다.

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

답변:

CREATE DATABASE newdb WITH TEMPLATE originaldb;

시도하고 테스트했습니다.

"부하 부족" DB 복사

저는 위의 예들과 함께 이 접근법을 만들었습니다.저는 "부하가 부족한" 서버에서 작업하고 있는데 @zbyzek에서 접근을 시도했을 때 오류가 발생했습니다.또한 "명령줄 전용" 솔루션을 찾고 있었습니다.

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

다음은 저에게 효과적인 방법입니다(출력을 파일로 이동하고 서버 연결 끊김으로부터 보호하는 명령 앞에 추가).

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

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

3. nohup psql exampledbclone_01 < example-01.sql

다음은 pgadmin4 GUI(백업 및 복원을 통해)만을 사용하여 데이터베이스를 통해 복사본을 만드는 전체 프로세스입니다.

Pgadmin4는 Postgres입니다.만약 당신이 macOS를 사용한다면 당신은 누를 수 있습니다.CMD+SPACE 및형을 합니다.pgadmin4실행할 수 있습니다.브라우저 탭이 크롬으로 열립니다.

---

복사 단계

백업 생성

데이터베이스 -> "백업"을 마우스 오른쪽 단추로 클릭하여 이 작업을 수행합니다.

파일 이름을 지정합니다.

맘에 들다test12345백업을 클릭합니다.덤프가 그면이 에 있지 . 파일 덤프가 생성되지 않습니다..sqlformat

다운로드한 위치 보기

화면 오른쪽 하단에 팝업이 표시됩니다.백업을 다운로드한 위치를 보려면 "자세한 정보" 페이지를 클릭하십시오.

다운로드한 파일의 위치 찾기

이경우, 은것그입니다./users/vincenttang

pgadmin에서 백업 복원

1~4단계를 올바르게 수행했다고 가정하면 이진 파일이 복원됩니다.동료가 로컬 컴퓨터에서 복원 파일을 사용하려고 할 수도 있습니다.그 사람이 pgadmin으로 가서 복원했다고 말했습니까?

데이터베이스 -> "복원"을 마우스 오른쪽 단추로 클릭하여 이 작업을 수행합니다.

파일 검색기 선택

파일 위치를 수동으로 선택해야 하며, pgadmin의 업로더 필드에 파일을 드래그 앤 드롭하지 마십시오.오류 권한이 발생할 수 있기 때문입니다.대신 방금 만든 파일을 찾습니다.

해당 파일 찾기

오른쪽 아래의 필터를 "모든 파일"로 변경해야 할 수도 있습니다.이후 4단계부터 파일을 찾습니다.이제 오른쪽 아래 "선택" 버튼을 눌러 확인합니다.

해당 파일 복원

파일 위치를 선택하면 이 페이지가 다시 나타납니다.어서 복원하십시오.

성공

모두 정상이면 오른쪽 아래에 성공적인 복원을 나타내는 표시기가 팝업됩니다.테이블로 이동하여 각 테이블의 데이터가 복원되었는지 확인할 수 있습니다.

성공적이지 못한 경우:

9단계가 실패할 경우 데이터베이스에서 이전 공용 스키마를 삭제해 보십시오."쿼리 도구"로 이동합니다.

다음 코드 블록 실행:

DROP SCHEMA public CASCADE; CREATE SCHEMA public;

이제 5단계에서 9단계를 다시 시도하십시오. 잘 될 것입니다.

편집 - 몇 가지 추가 참고 사항.복원 중에 "archiver header 1.14 unsupported version" 라인을 따라 업로드 중에 오류가 발생하면 PGADMIN4 업데이트

설명서에서, 사용createdb또는CREATE DATABASE템플릿을 사용하는 것은 권장되지 않습니다.

이름을 템플리트로 지정하여 template1 이외의 데이터베이스를 복사할 수 있지만, 이것은 (아직) 범용 "COPY DATABASE" 기능으로 의도되지 않았습니다.기본적인 제한사항은 템플릿 데이터베이스가 복사되는 동안 다른 세션을 연결할 수 없다는 것입니다.시작할 때 다른 연결이 있으면 CREATE DATABASE가 실패합니다. 그렇지 않으면 CREATE DATABASE가 완료될 때까지 템플리트 데이터베이스에 대한 새 연결이 잠깁니다.

pg_dump 또는 데이터베이스 및 모든 데이터를 복사할 수 있는 좋은 방법입니다.pgAdmin과 같은 GUI를 사용하는 경우 백업 명령을 실행할 때 이러한 명령이 백그라운드에서 호출됩니다.새 데이터베이스에 복사는 백업 및 복원의 두 단계로 이루어집니다.

pg_dumpallPostgreSQL 클러스터의 모든 데이터베이스를 저장합니다.이 방법의 단점은 데이터베이스를 생성하고 데이터를 채우는 데 필요한 SQL로 가득 찬 잠재적으로 매우 큰 텍스트 파일이 생성된다는 것입니다.이 접근 방식의 장점은 클러스터에 대한 모든 역할(권한)을 무료로 얻을 수 있다는 것입니다.모든 데이터베이스를 덤프하려면 슈퍼 사용자 계정에서 이 작업을 수행합니다.

pg_dumpall > db.out

복원할 수 있습니다.

psql -f db.out postgres

pg_dump에는 훨씬 더 작은 파일을 제공하는 몇 가지 압축 옵션이 있습니다. 데이터베이스가 . 에 두 cron 을 사용하여 cron 작업으로 합니다.

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

compress 레벨 ~ 및 압축레(0 ~ 9) 및입니다.create말한다pg_dump데이터베이스를 만드는 명령을 추가합니다. 새 클러스터로 )

pg_restore -d newdb db.dump

여기서 newdb는 사용할 데이터베이스의 이름입니다.

그 밖에 생각해야 할 것들

PostgreSQL은 ROLE을 사용하여 권한을 관리합니다.다음 사용자에 의해 복사되지 않았습니다.pg_dump또한 postgresql.conf 및 pg_hba.conf(데이터베이스를 다른 서버로 이동하는 경우)의 설정을 처리하지 않았습니다.당신은 스스로 conf 설정을 파악해야 할 것입니다.하지만 역할을 지원하기 위해 방금 발견한 속임수가 있습니다.역할은 클러스터 수준에서 관리되며, 요청할 수 있습니다.pg_dumpall이 있는 합니다.--roles-only명령줄 스위치.

여전히 관심이 있는 사람들을 위해, 저는 저자가 원하는 것을 (거의) 해주는 바시 스크립트를 생각해냈습니다.프로덕션 시스템에서 일일 비즈니스 데이터베이스 복사본을 만들어야 했는데, 이 스크립트가 효과가 있는 것 같습니다.데이터베이스 이름/사용자/pw 값을 변경해야 합니다.

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

PostgreSQL 9.1.2:

$ CREATEDB new_db_name -T orig_db_name -O db_user;

데이터베이스 덤프를 생성하려면 다음과 같이 하십시오.

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

데이터베이스 덤프를 복원하려면 다음과 같이 하십시오.

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

데이터베이스에 연결이 열려 있는 경우 이 스크립트가 도움이 될 수 있습니다.이를 사용하여 매일 밤 실운영 데이터베이스의 백업에서 테스트 데이터베이스를 만듭니다.이렇게 하면 프로덕션 DB의 .SQL 백업 파일이 있다고 가정합니다(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"

pgAdmin을 사용하여 템플릿으로 사용할 데이터베이스의 연결을 끊습니다.그런 다음 새 데이터베이스를 작성할 템플릿으로 선택하면 이미 사용 중인 오류가 발생하지 않습니다.

pgAdmin4:

1.복사할 DB를 선택하고 연결을 해제합니다.

"DB 연결 끊기"를 마우스 오른쪽 단추로 누릅니다.

2. 이전 DB 옆에 새 DB를 만듭니다.

• 이름을 대보세요.
• "definition" 탭에서 첫 번째 테이블을 템플릿으로 선택합니다(드롭다운 메뉴).

다시 연결하려면 create를 누르고 새 DB를 마우스 왼쪽 버튼으로 클릭하십시오.

전체 스키마를 복사하려면 다음 명령을 사용하여 pg_dump를 만들 수 있습니다.

pg_dump -h database.host.com -d database_name -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" -f sql_dump_to_import.sql

연결 문자열에 대한 추가 정보: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

또는 하나의 라이너로 결합합니다.

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. pgAdmin에서 기본 창을 연 다음 다른 쿼리 도구 창을 엽니다.
2. pgAdmin의 기본 창에서

템플리트로 사용할 "템플릿된" 데이터베이스의 연결을 끊습니다.

3. 쿼리 도구 창으로 이동

아래와 같이 2개의 쿼리를 실행합니다.

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

위의 SQL 문은 TemplateDB와의 모든 활성 세션을 종료한 후 새 TargetDB 데이터베이스를 생성할 템플릿으로 선택할 수 있습니다. 그러면 이미 사용 중인 오류가 발생하지 않습니다.

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

새 버전의 pgAdmin(4.30)은 템플릿에서 새 데이터베이스 작성을 지원합니다.새 데이터베이스 이름과 기존 템플리트 데이터베이스만 입력하면 됩니다.

TAMPLE originaldb OWNER dbuser를 사용하여 데이터베이스 newdb를 만듭니다.

Ubuntu를 사용하는 경우. 1 way created b -O Owner -Told_db_name new_db_name

2 way createdb test_copy pg_copy old_db_name | psql test_copy

사용해 보십시오.

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;

glXD

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