Migrating test database to production Postgres

Hello, I saw a link to this article. But when I run the migration commands that were suggested, I get this error message:
pg_dump: error: connection to server on socket “/var/snap/maas/common/postgres/sockets/.s.PGSQL.5432” failed: No such file or directory
Is the server running locally and accepting connections on that socket?

What am I missing?

Possibly a few things:

  1. pg_dump all requires SUPERUSER for the PostgreSQL role however, the maas role in the maas-test-db does not have that.

Note, I will use the -H flag for sudo because certain postgres commands like to switch working directories and sometime you wind up with harmless, but annoying errors like can’t cd to /root/…
Now some people do have sudo setup “differently” as far as what’s preserved from your env when your run sudo. So if the above does not work, do sudo -iu root rather than sudo -H

sudo -H psql -U postgres -h /var/snap/maas-test-db/common/postgres/sockets -d maasdb -c "ALTER ROLE maas WITH SUPERUSER"

Should spit back “ALTER ROLE

Now you can dump. Note, however, that snaps can only read from /var/snap/ and $HOME. If you’ll be restoring to another snapped PostgreSQL app, make sure you actually save then dump it to your home dir, otherwise you could be moving around a fairly large file. If moving to a deb-based PostgreSQL instance location won’t matter, as long as you have the correct permissions.

sudo -H pg_dumpall -U maas -l maasdb -h /var/snap/maas-test-db/common/postgres/sockets -c > ~/dump.sql

The file size will vary, mine was 8GB.

ls -altr ~/dump.sql -rw-rw-r-- 1 ubuntu ubuntu 8555677517 Nov 2 19:21 /home/ubuntu/dump.sql

Some guides will tell you to sudo -iu postgres for this step. Problem is the postgres UID has no rights to read from your home dir, so that will fail.

Just sudo as normal, using the -H option to preserve your SUDO_USER’s home.

Also, note that a pg_dumpall is exactly what is says, it dumps everything. The “maas” role and the “maasdb” used in that command were simply for authentication.

Since we are restoring every DB, not just maas, the target db is “postgres”, NOT “maasdb”

sudo -H psql -f ~/dump.sql postgres

Once that finishes, your MAAS DB won’t have a password (as the maas-test-db requires you to be on the node to access the DB), but it will still have SUPERUSER privileges :slight_smile:

Our target is postgres installed via app, using ubuntu packages. That means can now sudo as the “postgres” UID to give/change the maas roles password in order to all the region controllers to access to the DB.


If the MAAS instance has not changed, you’ll need to go add a line for database_pass: to /var/snap/maas/current/regiond.conf then restart MAAS with sudo snap restart maas

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.