Tworzenie kopii bazy danych w PostgreSQL
Jak poprawnie skopiować całą bazę danych (jej strukturę i dane) do nowej w pgAdmin?
16 answers
Postgres umożliwia użycie dowolnej istniejącej bazy danych na serwerze jako szablonu podczas tworzenia nowej bazy danych. Nie jestem pewien, czy pgAdmin daje Ci opcję w oknie Utwórz bazę danych, ale powinieneś być w stanie wykonać następujące czynności w oknie zapytania, jeśli tak nie jest:
CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
Mimo to możesz otrzymać:
ERROR: source database "originaldb" is being accessed by other users
Aby to naprawić, możesz użyć tego zapytania
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid();
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-06-04 20:37:42
Wersja wiersza poleceń odpowiedź Bella :
createdb -O ownername -T originaldb newdb
Powinno to być uruchamiane zgodnie z uprawnieniami mistrza bazy danych, zazwyczaj postgres.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:02:57
Aby sklonować istniejącą bazę danych za pomocą postgres możesz to zrobić
/* 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;
Zabije całe połączenie ze źródłowym db unikając błędu
ERROR: source database "SOURCE_DB" is being accessed by other users
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-04 11:59:57
W środowisku produkcyjnym, gdzie oryginalna baza danych jest w ruchu, używam po prostu:
pg_dump production-db | psql test-db
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-26 18:57:49
Nie wiem o pgAdmin, ale pgdump
daje zrzut bazy danych w SQL. Wystarczy utworzyć bazę danych o tej samej nazwie i wykonać
psql mydatabase < my dump
Aby przywrócić wszystkie tabele i ich dane oraz wszystkie uprawnienia dostępu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-10-21 20:32:53
Poskładałem to podejście razem z przykładami z góry. Pracuję na serwerze "pod obciążeniem" i dostałem błąd podczas próby podejścia z @ zbyszek. Byłem też po rozwiązaniu "tylko linia poleceń".
createdb: database creation failed: ERROR: source database "exampledb" is being accessed by other users
.
Oto, co zadziałało dla mnie (Polecenia dołączone do nohup
, aby przenieść wyjście do pliku i chronić przed rozłączeniem serwera):
nohup pg_dump exampledb > example-01.sql
-
createdb -O postgres exampledbclone_01
mój użytkownik to "postgres"
nohup psql exampledbclone_01 < example-01.sql
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-12 18:02:37
Pierwszy, sudo
jako użytkownik bazy danych:
sudo su postgres
Przejdź do Wiersza poleceń PostgreSQL:
psql
Utwórz nową bazę danych, nadaj prawa i zakończ:
CREATE DATABASE new_database_name;
GRANT ALL PRIVILEGES ON DATABASE new_database_name TO my_user;
\d
Kopiowanie struktury i danych ze starej bazy danych do nowej:
pg_dump old_database_name | psql new_database_name
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-03-23 15:26:58
W pgAdmin możesz utworzyć kopię zapasową z oryginalnej bazy danych, a następnie utworzyć nową bazę danych i przywrócić ją z właśnie utworzonej kopii zapasowej:
- Kliknij prawym przyciskiem myszy źródłową bazę danych, kopię zapasową... i wrzucić do pliku.
- Kliknij prawym przyciskiem myszy, nowy obiekt, Nowa baza danych... i wymień cel.
- Kliknij prawym przyciskiem myszy nową bazę danych, Przywróć... i wybierz plik.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-01-20 17:08:46
Jak poprawnie skopiować całą bazę danych (jej strukturę i dane) do nowej w pgAdmin?
ODPOWIEDŹ:
CREATE DATABASE newdb WITH TEMPLATE originaldb;
Wypróbowany i przetestowany.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-08-24 18:26:49
PostgreSQL 9.1.2:
$ CREATEDB new_db_name -T orig_db_name -O db_user;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-05-25 03:15:32
Dla tych, którzy nadal są zainteresowani, wymyśliłem skrypt bash, który robi (mniej więcej) to, co autor chciał. Musiałem zrobić codzienną kopię bazy danych biznesowych na systemie produkcyjnym, ten skrypt wydaje się działać. Pamiętaj o zmianie wartości bazy danych nazwa / użytkownik / 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"
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-01-09 14:42:18
Aby utworzyć zrzut bazy danych
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-11-21 13:29:22
Jeśli baza danych ma otwarte połączenia, ten skrypt może pomóc. Używam tego do tworzenia testowej bazy danych z kopii zapasowej bazy danych produkcji na żywo każdej nocy. To zakłada, że masz .Plik kopii zapasowej SQL z bazy produkcyjnej (robię to w Webminie).
#!/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"
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-08-06 14:59:46
Używając pgAdmin, odłącz bazę danych, której chcesz użyć jako szablonu. Następnie wybierz go jako szablon, aby utworzyć nową bazę danych, co pozwala uniknąć błędu już używanego.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-11-06 15:39:26
Z dokumentacji nie zaleca się używania createdb
lub CREATE DATABASE
z szablonami:
Chociaż możliwe jest skopiowanie bazy danych innej niż template1 przez określając jego nazwę jako szablon, nie jest to (jeszcze) zamierzone jako obiekt ogólnego przeznaczenia "COPY DATABASE". Głównym ograniczeniem jest że żadne inne sesje nie mogą być połączone z bazą szablonów podczas jest kopiowany. Tworzenie bazy danych nie powiedzie się, jeśli jakiekolwiek inne połączenie istnieje, gdy rozpoczyna; w przeciwnym razie nowe połączenia z szablonem baza danych jest zablokowana do momentu zakończenia tworzenia bazy danych.
pg_dump
lub pg_dumpall
jest dobrym sposobem na kopiowanie bazy danych i wszystkich danych. Jeśli używasz interfejsu graficznego, takiego jak pgAdmin, polecenia te są wywoływane za kulisami podczas wykonywania polecenia kopii zapasowej. Kopiowanie do nowej bazy danych odbywa się w dwóch fazach: Backup and Restore
pg_dumpall
zapisuje wszystkie bazy danych w klastrze PostgreSQL. Na wadą tego podejścia jest to, że kończy się potencjalnie bardzo dużym plikiem tekstowym pełnym SQL wymaganym do utworzenia bazy danych i wypełnienia danych. Zaletą tego podejścia jest to, że otrzymasz wszystkie role (uprawnienia) dla klastra za darmo. Aby zrzucić wszystkie bazy danych, zrób to z konta superużytkownika
pg_dumpall > db.out
I przywrócić
psql -f db.out postgres
pg_dump
ma kilka opcji kompresji, które dają znacznie mniejsze pliki. Mam produkcyjną bazę danych i kopię zapasową dwa razy dziennie z praca cron przy użyciu
pg_dump --create --format=custom --compress=5 ==file=db.dump mydatabase
Gdzie compress
jest poziomem kompresji (0 do 9) i create
mówi pg_dump, aby dodał polecenia do utworzenia bazy danych. Przywróć (lub Przenieś do nowego klastra) za pomocą
pg_restore -d newdb db.dump
Gdzie newdb to nazwa bazy danych, której chcesz użyć.
Inne rzeczy do przemyślenia
PostgreSQL używa ról do zarządzania uprawnieniami. Nie są one kopiowane przez pg_dump
. Nie mamy również do czynienia z ustawieniami w postgresql.conf oraz pg_hba.conf (jeśli przenosisz bazę danych na inny serwer). Musisz samodzielnie ustalić ustawienia konf. Ale jest pewna sztuczka, którą odkryłem, żeby tworzyć kopie zapasowe ról. Role są zarządzane na poziomie klastra i możesz poprosić pg_dumpall
o wykonanie kopii zapasowej tylko ról za pomocą przełącznika linii poleceń --roles-only
.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-15 16:54:44
Spróbuj tego:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-12-16 16:36:06