Running Sqitch to deploy a database in Postgres on a Docker container

Question

I am running Postgres on a Docker container.

I have been trying to following the example show to deploy scripts using Sqitch from the following page:

https://metacpan.org/pod/sqitchtutorial

Now I have got as far as creating my folders for Deploy, Revert and Verify

I then try to deploy changes using the following command:

sqitch deploy db:pg://username:password@localhost/flipr_test

where i pass in my username and password

However i get the following error message:

Adding registry tables to db:pg://postgres:@localhost/flipr_test
"/opt/local/pgsql/bin/psql" failed to start: "The system cannot find the path specified"

How can I get Sqitch to run to deploy a database to Postgres running on a Docker Container?

Answer 1

Need to do the following :

1. Get a Sqitch.bat file using the following command wget https://git.io/JTAi6 -o sqitch.bat
2. Copy this Sqitch.bat file into the flipr folder.

This sqitch file runs Sqitch in a Docker Container

3. Add the following line to docker-compose.yml for the Postgres Database (Under 'environment'): environment:

- POSTGRES_HOST_AUTH_METHOD=trust 

4. Then in ~/.sqitch/sqitch.conf

Comment out the following line:

#client = /opt/local/pgsql/bin/psql

This means that psql will then be used from the Postgres container

5. Now I can run the following command

.\sqitch.bat deploy db:pg://postgres:password!@localhost/flipr_test

Here the sqitch.bat is running up Sqitch in the Docker Container and then from there can run the deploy command to deploy changes

Answer 2

Not really an answer, just a long comment describing how to troubleshoot/fix this. Sqitch uses database native clients to connect to the respective engine. In the Postgres case that is psql. You need to have psql installed and accessible when you run Sqitch commands against the engine. As a for instance, in Ubuntu/Debian that would be installing the postgresql-client package. You have not mentioned what OS you are using so the install maybe different. The psql client would also need to be 'seen' by the sqitch deploy command wherever you are running that. You don't indicate where sqitch deploy is being run, on the Docker host or in the Docker container. In either case psql needs to be present at the path specified for the command run. In your case either psql is not installed or if installed it is at path other then /opt/local/pgsql/bin/psql. In the tutorial search for sqitch config --user engine.pg.client to see how you can change the path.