Kopie einer Datenbank in PostgreSQL erstellen

Eine Befehlszeilenversion von Bell's Antwort :

createdb -O ownername -T originaldb newdb

Dies sollte unter den Privilegien des Datenbankmasters ausgeführt werden, normalerweise Postgres.

Um eine vorhandene Datenbank mit Postgres zu klonen, können Sie dies tun 

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

Die IT-Abteilung bricht die gesamte Verbindung zur Quelldatenbank ab, um den Fehler zu vermeiden

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

In der Produktionsumgebung, in der die Originaldatenbank unter Verkehr steht, verwende ich einfach:

pg_dump production-db | psql test-db

Ich kenne pgAdmin nicht, aber pgdump gibt Ihnen einen Speicherauszug der Datenbank in SQL. Sie müssen nur eine Datenbank mit demselben Namen erstellen und tun 

psql mydatabase < my dump

alle Tabellen und ihre Daten sowie alle Zugriffsrechte wiederherstellen.

Zuerst Sudo als Datenbankbenutzer:

Sudo su postgres

Gehen Sie zur PostgreSQL-Befehlszeile:

psql

Erstellen Sie die neue Datenbank, vergeben Sie die Rechte und beenden Sie:

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

Struktur und Daten aus der alten Datenbank in die neue kopieren:

pg_dump old_database_name | psql new_database_name

Ich habe diesen Ansatz zusammen mit den Beispielen von oben zusammengestellt. Ich arbeite an einem "under load" -Server und habe den Fehler erhalten, als ich den Ansatz von @zbyszek versuchte. Ich war auch nach einer "nur Befehlszeile" Lösung. 

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

Folgendes funktionierte für mich ( Befehle, denen Nohup vorangestellt ist, um die Ausgabe in eine Datei zu verschieben und vor einem Serverabbruch zu schützen ):

1. Nohup pg_dump exampledb > example-01.sql
2. createdb -O postgres exampledbclone_01</ p>

   mein Benutzer ist "postgres" </ li>

   Nohup psql exampledbclone_01 < example-01.sql

   </ ol>

In pgAdmin können Sie eine Sicherung von Ihrer ursprünglichen Datenbank erstellen und dann einfach eine neue Datenbank erstellen und die gerade erstellte Sicherung wiederherstellen:

1. Klicken Sie mit der rechten Maustaste auf die Quellendatenbank, Sichern ... und legen Sie eine Datei ab.
2. Klicken Sie mit der rechten Maustaste auf Neues Objekt, Neue Datenbank ... und benennen Sie das Ziel.
3. Klicken Sie mit der rechten Maustaste auf die neue Datenbank, Wiederherstellen ... und wählen Sie Ihre Datei aus.

Wie kann ich die gesamte Datenbank (ihre Struktur und Daten) in pgAdmin auf eine neue kopieren?

Antworten:

CREATE DATABASE newdb WITH TEMPLATE originaldb;

Ausprobiert und getestet.

PostgreSQL 9.1.2:

$ CREATEDB new_db_name -T orig_db_name -O db_user;

Für diejenigen, die noch interessiert sind, habe ich ein Bash-Skript entwickelt, das (mehr oder weniger) das tut, was der Autor wollte. Ich musste eine tägliche Geschäftsdatenbankkopie auf einem Produktionssystem erstellen. Dieses Skript scheint den Trick zu erfüllen. Denken Sie daran, den Datenbanknamen/Benutzer/pw-Werte zu ändern.

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

So erstellen Sie einen Datenbank-Dump

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

Um den Datenbankspeicherabzug erneut zu speichern 

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

Aus der Dokumentation wird die Verwendung von createdb oder CREATE DATABASE mit Vorlagen nicht empfohlen:

Es ist zwar möglich, eine andere Datenbank als template1 mit .__ zu kopieren. Wenn Sie den Namen als Vorlage angeben, ist dies (noch) nicht als .__ gedacht. Allgemeine "COPY DATABASE" -Funktion. Die hauptsächliche Einschränkung ist Es können keine anderen Sitzungen mit der Vorlagendatenbank verbunden werden, während es wird kopiert. CREATE DATABASE schlägt fehl, wenn eine andere Verbindung vorhanden ist existiert, wenn es beginnt; ansonsten neue Verbindungen zur Vorlage Die Datenbank wird gesperrt, bis CREATE DATABASE abgeschlossen ist.

pg_dump oder pg_dumpall ist ein guter Weg, um die Datenbank UND ALLE DATEN zu kopieren. Wenn Sie eine GUI wie pgAdmin verwenden, werden diese Befehle beim Ausführen eines Sicherungsbefehls im Hintergrund aufgerufen. Das Kopieren in eine neue Datenbank erfolgt in zwei Phasen: Sichern und Wiederherstellen

pg_dumpall speichert alle Datenbanken im PostgreSQL-Cluster. Der Nachteil dieses Ansatzes ist, dass Sie eine potenziell sehr große Textdatei mit SQL benötigen, die zum Erstellen der Datenbank und zum Auffüllen der Daten erforderlich ist. Der Vorteil dieses Ansatzes besteht darin, dass Sie alle Rollen (Berechtigungen) für den Cluster kostenlos erhalten. Um alle Datenbanken zu sichern, machen Sie dies vom Superuser-Konto aus

pg_dumpall > db.out

und wieder herzustellen

psql -f db.out postgres

pg_dump verfügt über einige Komprimierungsoptionen, die Ihnen wesentlich kleinere Dateien ermöglichen. Ich habe eine Produktionsdatenbank, die ich zweimal täglich mit einem Cron-Job gesichert habe

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

dabei ist compress die Komprimierungsstufe (0 bis 9) und create weist pg_dump an, Befehle zum Erstellen der Datenbank hinzuzufügen. Wiederherstellen (oder in neuen Cluster verschieben) mit

pg_restore -d newdb db.dump

dabei ist newdb der Name der Datenbank, die Sie verwenden möchten.

Andere Dinge zum Nachdenken

PostgreSQL verwendet ROLES zum Verwalten von Berechtigungen. Diese werden nicht von pg_dump kopiert. Außerdem haben wir uns nicht mit den Einstellungen in postgresql.conf und pg_hba.conf beschäftigt (wenn Sie die Datenbank auf einen anderen Server verschieben). Sie müssen die Conf-Einstellungen selbst bestimmen. Aber es gibt einen Trick, den ich gerade entdeckt habe, um Rollen zu sichern. Rollen werden auf Clusterebene verwaltet, und Sie können pg_dumpall mit der Befehlszeilenoption --roles-only nur die Rollen sichern. 

Wenn die Datenbank offene Verbindungen hat, kann dieses Skript hilfreich sein. Ich verwende dies, um jede Nacht eine Testdatenbank aus einer Sicherung der Live-Produktionsdatenbank zu erstellen. Dies setzt voraus, dass Sie eine .SQL-Sicherungsdatei aus der Produktionsdatenbank haben (ich mache dies innerhalb von 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"

Trennen Sie mit pgAdmin die Datenbank, die Sie als Vorlage verwenden möchten. Anschließend wählen Sie sie als Vorlage zum Erstellen der neuen Datenbank aus. Dadurch wird vermieden, dass der Fehler bereits verwendet wird. 

Wenn Sie das gesamte Schema kopieren möchten, können Sie mit folgendem Befehl einen pg_dump erstellen:

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

Und wenn Sie diesen Speicherauszug importieren möchten, können Sie Folgendes verwenden:

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

Weitere Informationen zu Verbindungszeichenfolgen: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

Oder kombinieren Sie es einfach in einem 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. Öffnen Sie das Hauptfenster in pgAdmin und öffnen Sie dann ein anderes Abfrage-Tool-Fenster
2. In den Hauptfenstern von pgAdmin

Trennen Sie die "Vorlage" -Datenbank, die Sie als Vorlage verwenden möchten.

3. Gehe zum Abfrage-Tools-Fenster

Führen Sie 2 Abfragen wie folgt aus

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

(Die obige SQL-Anweisung beendet alle aktiven Sitzungen mit TemplateDB. Anschließend können Sie sie als Vorlage zum Erstellen der neuen TargetDB-Datenbank auswählen. Auf diese Weise wird vermieden, dass der bereits verwendete Fehler angezeigt wird.)

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