複製 - PostgreSQLでのデータベースのコピーの作成




ポスグレ コピー (11)

PostgreSQL 9.1.2:

$ CREATEDB new_db_name -T orig_db_name -O db_user;

データベース全体(構造とデータ)を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();

pgAdminを使用して、テンプレートとして使用するデータベースを切断します。 次に、それをテンプレートとして選択して新しいデータベースを作成します。これにより、すでに使用中のエラーが表示されなくなります。


documentationから、 createdbまたはCREATE DATABASEをテンプレートで使用することはお勧めしません。

テンプレート1の名前をテンプレートとして指定することで、template1以外のデータベースをコピーすることは可能ですが、これは汎用の「COPY DATABASE」機能ではありません。 基本的な制限は、テンプレートデータベースがコピーされている間に他のセッションを接続できないことです。 起動時に他の接続が存在する場合、CREATE DATABASEは失敗します。 それ以外の場合は、CREATE DATABASEが完了するまで、テンプレート・データベースへの新規接続がロックアウトされます。

postgresql.org/docs/current/static/app-pgdump.htmlまたはpg_dumpallは、データベースとすべてのデータをコピーするための良い方法です。 pgAdminのようなGUIを使用している場合、バックアップコマンドを実行すると、これらのコマンドが舞台裏で呼び出されます。 新しいデータベースへのコピーは、バックアップとリストアの2つのフェーズで実行されます。

pg_dumpallはすべてのデータベースをPostgreSQLクラスタに保存します。 この方法の欠点は、データベースを作成してデータを取り込むために必要な非常に大きなテキスト・ファイルがSQLに満ちてしまうことです。 この方法の利点は、クラスタのすべての役割(権限)を無料で取得できることです。 すべてのデータベースをダンプするには、スーパーユーザーアカウント

pg_dumpall > db.out

復元する

psql -f db.out postgres

pg_dumpは小さなファイルを与える圧縮オプションがいくつかあります。 私は生産データベースを持っています。

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は権限を管理するためにROLESを使用します。 これらはpg_dumpによってコピーされません。 また、 postgresql.confpg_hba.conf (データベースを別のサーバに移動している場合)の設定は扱っていません。 自分でconfの設定を把握する必要があります。 しかし、私はちょうどロールをバックアップするために発見したトリックがあります。 ロールはクラスタレベルで管理され、 --roles-onlyコマンドラインスイッチでロールだけをバックアップするようpg_dumpallに問い合わせることができます。


これを試して:

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


まず、データベースユーザーとして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

データベース・ダンプを作成するには

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

データベース全体(構造とデータ)をpgAdminの新しいデータベースにコピーする正しい方法は何ですか?

回答:

CREATE DATABASE newdb WITH TEMPLATE originaldb;

試してみた。


既存のデータベースをポストグルでクローンするには、そうすることができます

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

興味のある人には、著者が望む(多かれ少なかれ)bashスクリプトを思いついた。 私は毎日のビジネスデータベースを本番システムにコピーしなければなりませんでしたが、このスクリプトはそのトリックを行うようです。 データベース名/ユーザー/ 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