Manage remote Postgres SQL database with Docker
I dont’ want to install lots of programs to be able to manage a remote database from my local computer. I already have Docker installed, so I am planning to use an existing image for the database administration software.
Looking around the web, at first I found adminer, but after trying the Export feature, it threw some errors at when I imported the backup again to a new database stating:
PDO::quote() expects parameter 1 to be string
So, instead I will use pgAdmin, with an existing Docker image containing pgAdmin 4.
As stated in the readme of the pgAdmin Docker image, we can start the container like this
$ docker run --rm -p 5050:5050 thajeztah/pgadmin4
Then browse the web interface locally from
http://localhost:5050. But some features will not work without mounting some additional binaries to the container (e.g.
pg_restore). So I’m downloading all the pre-compiled binaries from the official postgres website and extracting them to a user readable folder.
So stopping the container, then performing some steps locally to get what I need:
$ mkdir -p ~/tmp/postgres $ cd ~/tmp/postgres $ curl -O https://get.enterprisedb.com/postgresql/postgresql-9.6.4-1-linux-x64-binaries.tar.gz $ tar -xf postgresql-9.6.4-1-linux-x64-binaries.tar.gz $ ls pgsql/bin/ clusterdb ecpg pg_ctl pg_restore pgbench psql.bin createdb initdb pg_dump pg_rewind pltcl_delmod reindexdb createlang oid2name pg_dumpall pg_standby pltcl_listmod vacuumdb createuser pg_archivecleanup pg_isready pg_test_fsync pltcl_loadmod vacuumlo dropdb pg_basebackup pg_receivexlog pg_test_timing postgres droplang pg_config pg_recvlogical pg_upgrade postmaster dropuser pg_controldata pg_resetxlog pg_xlogdump psql
Now mount the binaries to the container. The volume syntax here is
-v [local path]:[container mount path], which means I am mounting the binaries we just extracted into
/pgbin of the container filesystem.
$ docker run --rm -v ~/tmp/postgres/pgsql/bin:/pgbin -p 5050:5050 thajeztah/pgadmin4
While the container is running, we can verify that the files were actually mounted by executing
ls on the container. First checking what the name of the container is:
$ docker ps docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 908b146e9703 thajeztah/pgadmin4 "python ./usr/loca..." 4 minutes ago Up 4 minutes 0.0.0.0:5050->5050/tcp cocky_keller
So now we can execute
ls on the container
$ docker exec cocky_keller ls /pgbin createdb createlang droplang oid2name [...]
Now, to make use of these tools, open up a browser with http://localhost:5050 and go to
File->Preferences->Paths->Binary paths, and enter
/pgbin into both path fields.
Now I can import and export my databases.