Postgres restore na een pg_dumpall

[mithras]

Voor thuis gebruik ik @Firefly III om financien inzichtelijk te krijgen. Het systeem draait op 2 docker containers, applicatie + database. De database is een postgres:10-alpine container.

Als backup gebruikte ik pg_dumpall om de hele db naar een SQL plaintext bestand weg te schrijven. Ik heb ooit een restore nodig gehad en dat is op een of andere manier gelukt. Helaas, nu totaal niet meer

Het backup "script":

docker exec \
-t firefly-iii-db \
pg_dumpall -c -U firefly \
> backups/dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

Dan een restore:

docker exec -i firefly-iii-db psql -U firefly  < $1

Een backup file begint dan als volgt:

code:

--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Drop databases
--

DROP DATABASE firefly;

--
-- Drop roles
--

DROP ROLE firefly;

--
-- Roles
--

CREATE ROLE firefly;
ALTER ROLE firefly WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'asd1234hihiditishemniet';

--
-- Database creation
--

CREATE DATABASE firefly WITH TEMPLATE = template0 OWNER = firefly;
REVOKE CONNECT,TEMPORARY ON DATABASE template1 FROM PUBLIC;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;

\connect firefly

SET default_transaction_read_only = off;

Dit lukt niet door de melding "cannot drop de currently open database":

SET
SET
SET
ERROR:  cannot drop the currently open database
ERROR:  current user cannot be dropped
ERROR:  role "firefly" already exists
ALTER ROLE
ERROR:  database "firefly" already exists
REVOKE
GRANT
You are now connected to database "firefly" as user "firefly".

Ik heb mn container + volume al eens weggegooid om een verse schone install vanuit de applicatie te krijgen. Ik heb de database via CLI weggegooid (psql DROP DATABASE) maar toen kreeg ik helemaal niets meer voor elkaar.

Ik zie wel oplossingen met pg_dump en pg_restore, maar daar is het nu te laat voor: ik heb met pg_dumpall wel alles in een SQL script staan maar nu met een verwijderde database kan ik geen pg_dump meer draaien...


De vraag is nu, kan ik bijv mijn output van pg_dumpall in een ander format krijgen zodat ik pg_restore kan gebruiken? Of kan ik de output van pg_dumpall zo aanpassen dat psql het slikt? Of moet ik nog eea voorbereiden met eigen commando's voordat ik een psql op deze dump kan toepassen?

AanvullingIk heb alle create table statements uit de sql gehaald en nogmaals een psql gedraaid met dus alleen de data. Ik kreeg wel wat errors op duplicate keys, dus ben nog niet helemaal overtuigd, maar het lijkt wel te lukken (ik kan inloggen met mijn credentials en zie data) dus wellicht is dit een prima hack geweest, maar ben nog niet gerust op dat dit een stabiele oplossing is

[ Voor 7% gewijzigd door mithras op 14-07-2020 16:10 ]

[Hero of Time]

Je logt in met een gebruiker die je opnieuw rechten wit toekennen. Dat werkt natuurlijk niet. Dat kan je alleen doen als je een andere gebruik bent, zoals de postgres gebruiker.

Is er een reden waarom je dumpall gebruikt ipv gewoon dump? Want je hebt maar 1 database, het is niet nodig om de systeemdatabase ook nog te dumpen, er staat niets zinvols in.

Als je in de toekomst weer zoiets doet en de database dropt buiten het script om, gebruik dan de -C optie om de doeldatabase aan te maken als deze niet bestaat. Zie de help uitvoer hiervan of de man-page voor meer informatie.

Standaard kan je trouwens met pgdump prima een restore doen. Maar de structuur van pgdumpall is anders, omdat het alle rollen e.d. erbij heeft ipv alleen je database. Die paar dingetjes kan je ook zelf regelen als dat nodig is, dus maak gewoon een normale dump ipv een volledige dump met alles van de server. Vergeet niet om de databasenaam op te geven bij de dump.