postgresql
Säkerhetskopiering och återställning
Sök…
Anmärkningar
Säkerhetskopiera filsystemet istället för att använda pg_dumpall och pg_dump
Det är väldigt viktigt att om du använder den här, anropar pg_start_backup() funktionen pg_start_backup() före och pg_stop_backup() efter. Att göra säkerhetskopior av filsystem är inte säkert annars; till och med en ZFS- eller FreeBSD-stillbild av filsystemet som säkerhetskopieras utan dessa funktionssamtal kommer att placera databasen i återställningsläge och kan förlora transaktioner.
Jag skulle undvika att göra filsystem-säkerhetskopior istället för vanliga Postgres-säkerhetskopior, både av detta skäl och eftersom Postgres-säkerhetskopior (särskilt i det anpassade formatet) är extremt mångsidiga för att stödja alternativa återställningar. Eftersom de är enstaka filer, är de också mindre krångel att hantera.
Säkerhetskopiera en databas
pg_dump -Fc -f DATABASE.pgsql DATABASE
-Fc väljer "anpassat reservformat" som ger dig mer kraft än rå SQL; se pg_restore för mer information. Om du vill ha en vanilj SQL-fil kan du göra det istället:
pg_dump -f DATABASE.sql DATABASE
eller ens
pg_dump DATABASE > DATABASE.sql
Återställa säkerhetskopior
psql < backup.sql
Ett säkrare alternativ använder -1 att radera återställningen i en transaktion. -f anger filnamnet snarare än att använda skalomföring.
psql -1f backup.sql
Anpassade formatfiler måste återställas med pg_restore med alternativet -d att ange databasen:
pg_restore -d DATABASE DATABASE.pgsql
Det anpassade formatet kan också konverteras tillbaka till SQL:
pg_restore backup.pgsql > backup.sql
Användning av det anpassade formatet rekommenderas eftersom du kan välja vilka saker som ska återställas och eventuellt möjliggöra parallellbehandling.
Du kan behöva göra en pg_dump följt av en pg_restore om du uppgraderar från en postgresql-version till en nyare.
Säkerhetskopiera hela klustret
$ pg_dumpall -f backup.sql
Detta fungerar bakom kulisserna genom att göra flera anslutningar till servern en gång för varje databas och köra pg_dump på den.
Ibland kan du bli frestad att ställa in detta som ett cron-jobb, så du vill se det datum då säkerhetskopian togs som en del av filnamnet:
$ postgres-backup-$(date +%Y-%m-%d).sql
Observera dock att detta kan producera stora filer dagligen. Postgresql har en mycket bättre mekanism för regelbundna säkerhetskopior - WAL-arkiv
Utgången från pg_dumpall är tillräcklig för att återställa till en identiskt konfigurerad Postgres-instans, men konfigurationsfilerna i $PGDATA ( pg_hba.conf och postgresql.conf ) är inte en del av säkerhetskopian, så du måste säkerhetskopiera dem separat.
postgres=# SELECT pg_start_backup('my-backup');
postgres=# SELECT pg_stop_backup();
För att ta en filsystem-säkerhetskopia måste du använda dessa funktioner för att säkerställa att Postgres är i ett enhetligt tillstånd medan säkerhetskopian är förberedd.
Använd kopia för att importera
För att kopiera data från en CSV-fil till en tabell
COPY <tablename> FROM '<filename with path>';
För att infoga i user från en fil med namnet user_data.csv placerad inuti /home/user/ :
COPY user FROM '/home/user/user_data.csv';
För att kopiera data från rörseparerad fil till tabell
COPY user FROM '/home/user/user_data' WITH DELIMITER '|';
Obs: I avsaknad av alternativet with delimiter är standardavgränsaren komma ,
Att ignorera rubrikraden vid import av fil
Använd alternativet Header:
COPY user FROM '/home/user/user_data' WITH DELIMITER '|' HEADER;
Obs! Om data är citerade, är datatecken som standard citationstecken dubbelcitationstecken. Om uppgifterna är citerade med något annat tecken, använd alternativet QUOTE ; men detta alternativ är endast tillåtet när du använder CSV-format.
Använd kopia för att exportera
För att kopiera tabell till standard o / p
KOPIERA <tablename> TO STDOUT (DELIMITER '|');
Så här exporterar du tabellanvändare till Standarduppsättning:
KOPIERA användare till STDOUT (DELIMITER '|');
För att kopiera tabell till fil
KOPIERA användare FRA '/ home / user / user_data' MED DELIMITER '|';
För att kopiera utgången från SQL-sats till fil
COPY (sql statement) TO '<filnamn med sökväg>';
KOPIERA (VÄLJ * FRÅN användaren VAR användarnamn Gillar 'A%') TILL '/ home / user / user_data';
För att kopiera till en komprimerad fil
KOPIERA användare till PROGRAM 'gzip> /home/user/user_data.gz';
Här körs program gzip för att komprimera användartabelldata.
Använda psql för att exportera data
Data kan exporteras med kopieringskommando eller med hjälp av kommandoradsalternativ för psql-kommandot.
Så här exporterar du csv-data från tabellanvändare till csv-fil:
psql -p \<port> -U \<username> -d \<database> -A -F<delimiter> -c\<sql to execute> \> \<output filename with path>
psql -p 5432 -U postgres -d test_database -A -F, -c "select * from user" > /home/user/user_data.csv
Här gör kombinationen av -A och -F tricket.
-F är att ange avgränsare
-A or --no-align
Växlar till ojusterat utgångsläge. (Standardutgångsläget är annars anpassat.)