How to delete a postgres database?

Questions / Help
1

We have a database cluster that contains the databases for each of our preview deployments.
We basically have one database per environment.

When the preview deployment is closed, we want to delete the database and user.

We can do that if we connect to the cluster and run the postgres commands manually like this :

❯ flyctl postgres connect -a app-pg-staging      
Connecting to ..... complete
psql (15.3 (Debian 15.3-1.pgdg120+1))
Type "help" for help.

postgres=#  drop database "app-123 with (force);
postgres=#  drop user "app-123";

Now we want to run this in a Github Action, and we can’t seem to figure it out.
We connect to the cluster but we have no idea how to pass the commands to postgres.

    - name: Destroy Postgres
        run: |
          flyctl postgres connect -a app-pg-staging
          drop database "app-${{ github.event.number }}" with (force);
          drop user "app-${{ github.event.number }}";

Obviously this doesn’t work. I’m looking for a way to pass the command string to postgres…

2

Hey @depsimon! There isn’t a way to pass a command string to the fly postgres connect command directly, but you should be able to accomplish this using the fly ssh console command which does accept command strings.

Under the hood the postgres connect command is just opening a psql terminal on your machine. If you pass the equivalent psql command to the fly ssh console command, it’ll execute it as expected. In psql you can chain multiple commands with multiple -c flags

Something like this should get what you need:

fly ssh console  --command 'psql postgres://user:password@localhost:5432 -c "drop database "app-${{ github.event.number }}" with (force);" -c "drop user "app-${{ github.event.number }}";"'

That’ll start an ssh session to your fly machine, execute the psql command, and exit when done.

3

Thanks @Sam-Fly I’ll try that.
I’m unsure how I can get the postgres DB URL at this stage, as it’s stored in the app’s secrets.

4

If you have an app attached to that postgres DB already, you can ssh into the app and run env | grep DATABASE_URL it will return the database connection string. If you don’t have an app attached to it already, attaching one with fly pg attach <db_name> will add it as a secret and print the connection string to your console.

For running the psql command on the database machine you’ll just need to swap out the <appname>.flycast portion of the connection string for localhost.

5

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