Wie kann ich die gesamte Datenbank (ihre Struktur und Daten) in pgAdmin auf eine neue kopieren?
Postgres erlaubt die Verwendung einer vorhandenen Datenbank auf dem Server als Vorlage beim Erstellen einer neuen Datenbank. Ich bin nicht sicher, ob pgAdmin Ihnen die Option für das Dialogfeld "Datenbank erstellen" gibt, Sie sollten jedoch Folgendes in einem Abfragefenster ausführen können, falls dies nicht der Fall ist:
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
Dennoch können Sie erhalten:
ERROR: source database "originaldb" is being accessed by other users
Um dies zu beheben, können Sie diese Abfrage verwenden
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();
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 ):
Nohup pg_dump exampledb > example-01.sql
createdb -O postgres exampledbclone_01
</ p>mein Benutzer ist "postgres" </ li>
- </ ol>
Nohup psql exampledbclone_01 < example-01.sql
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:
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.
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”
Trennen Sie die "Vorlage" -Datenbank, die Sie als Vorlage verwenden möchten.
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;