dokku postgres:export [db_name] > dump.sql (CLI) loop in local

[dalou]

with dokku cli postgres:import command seems to work but its loop over dump file writing, the same issue with direct output too. ex : @local_machine# dokku postgres:export [db_name] | pg_restore -U postgres -d [local_db_name]

Is there any otherway to synchronize distant postgres DB with local postgres via CLI ?

[josegonzalez]

What do you mean, "it loops"?

[dalou]

its write (or output) the file again, and again, and again, its never exit. an infinite loop. Try the command, u will see.

[josegonzalez]

Seems to work fine here. Latest install of the plugin. What happens if you just export without importing?

root@dokku:/var/lib/dokku/services/postgres# dokku postgres:create derp
       Waiting for container to be ready
       Creating container database
       Securing connection to database
=====> Postgres container created: derp
       DSN: postgres://postgres:2ce17d116f27ebf8c740db9aabac672a@dokku-postgres-derp:5432/derp

root@dokku:/var/lib/dokku/services/postgres# dokku postgres:export derp > derp.db

root@dokku:/var/lib/dokku/services/postgres# cat derp.db
PGDMP
    4tderp9.5.29.5.20ENCODINENCODINGSET client_encoding = 'UTF8';
false00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false126216384derDATABASEtCREATE DATABASE derp WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';
DROP DATABASE derp;
postgresfalse26152200publicSCHEMACREATE SCHEMA public;
DROP SCHEMA public;
SCHEMA publicCOMMENT6COMMENT ON SCHEMA public IS 'standard public schema';
postgresfalse6307912361plpgsql  EXTENSION?CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
DROP EXTENSION plpgsql;
false00EXTENSION plpgsqlCOMMENT@COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
false1

[dalou]

its happen with the dokku CLI from a local machine. now you have created derp on a distant server,

root@dokku:/var/lib/dokku/services/postgres# dokku postgres:create derp

try an export with the cli (contrib/dokku_client.sh), like that : local@local:/path/to/your/git/remoted/project/# dokku postgres:export derp > derp.db

I notice the goal : make an easy command to synchronize local DB from production DB, like the heroku pg:pull currently i need those 3 lines to get work:

dropdb derp -U postgres --if-exists
createdb derp -U postgres
ssh root@[myserver] "dokku postgres:export derp" | pg_restore -U postgres -d derp

[josegonzalez]

Works using the official remote client:

jose@mactaku:~/src/side-projects/playground git:master $ dokku postgres:export derp > derp.db

jose@mactaku:~/src/side-projects/playground git:master $ cat derp.db
PGDMP
    'tderp9.5.29.5.20ENCODINENCODINGSET client_encoding = 'UTF8';
false00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false126216384derDATABASEtCREATE DATABASE derp WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';
DROP DATABASE derp;
postgresfalse26152200publicSCHEMACREATE SCHEMA public;
DROP SCHEMA public;
SCHEMA publicCOMMENT6COMMENT ON SCHEMA public IS 'standard public schema';
postgresfalse6307912361plpgsql  EXTENSION?CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
DROP EXTENSION plpgsql;
false00EXTENSION plpgsqlCOMMENT@COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
false1

[josegonzalez]

Using the root user:

jose@mactaku:~/src/side-projects/playground git:master $ ssh root@dokku.josediazgonzalez.com "dokku postgres:export derp"
PGDMP
    -tderp9.5.29.5.20ENCODINENCODINGSET client_encoding = 'UTF8';
false00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false126216384derDATABASEtCREATE DATABASE derp WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';
DROP DATABASE derp;
postgresfalse26152200publicSCHEMACREATE SCHEMA public;
DROP SCHEMA public;
SCHEMA publicCOMMENT6COMMENT ON SCHEMA public IS 'standard public schema';
postgresfalse6307912361plpgsql  EXTENSION?CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
DROP EXTENSION plpgsql;
false00EXTENSION plpgsqlCOMMENT@COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
false1

Using the dokku user:

jose@mactaku:~/src/side-projects/playground git:master $ ssh dokku@dokku.josediazgonzalez.com "postgres:export derp"
PGDMP
    7tderp9.5.29.5.20ENCODINENCODINGSET client_encoding = 'UTF8';
false00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false126216384derDATABASEtCREATE DATABASE derp WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';
DROP DATABASE derp;
postgresfalse26152200publicSCHEMACREATE SCHEMA public;
DROP SCHEMA public;
SCHEMA publicCOMMENT6COMMENT ON SCHEMA public IS 'standard public schema';
postgresfalse6307912361plpgsql  EXTENSION?CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
DROP EXTENSION plpgsql;
false00EXTENSION plpgsqlCOMMENT@COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
false1jose@mactaku:~/src/side-projects/playground git:master $

[dalou]

Ok it works for you :) For me the second works (root and dokku user), but the first with the official RC make an infinite loop.

`➜ TEST git:(master) ✗

dokku postgres:export derp > derp.db` ... ... ... ... ... never ending ...

[josegonzalez]

What version of the plugin do you have? Can you do the following on your server:

cd /var/lib/dokku/plugins/available/postgres
git show

And paste the output?

[dalou]

cd /var/lib/dokku/services/postgres
fatal: Not a git repository (or any of the parent directories): .git

but i clone the plugin 1 day ago

but for my problem, my bad, i just incredibly slow, i think, it's just curious for a fresh DB the dump file is over 300.000.000 lines. I continue to investigate

[josegonzalez]

Sorry, wrong directory. You probably have the correct version though.

Regarding speed, it is probably buffering the output in memory and then spitting it out.