How to interact with your project’s database¶

    • in a Docker container for your local projects: Interact with the local database
    • on a dedicated cluster for your Cloud-deployed sites:
      In either case, you will mostly only need to interact with the database via Django. However, if youneed to interact with it directly, the option exists.

    This is the recommended and most useful way to interact with the project’s database.

    Using dbshell¶

    Run:

    Connecting to the database manually¶

    You can also make the connection manually from within the container, for example:

    1. docker-compose run --rm web psql -h postgres -U postgres db

    As well as psql you can run commands such as pg_dump and pg_restore. This is usefulfor a number of common operations, below.

    Using docker exec¶

    Another way of interacting with the database is via the database container itself, using docker
    exec    . This requires that the database container already be up and running.

    For example, if your database container is called example_db_1:

    1. docker exec -i example_db_1 psql -U postgres

    From your host environment¶

    If you have a preferred Postgres management tool that runs on your own computer, you can alsoconnect to the database from outside the application.

    Expose the database’s port¶

    In order to the connect to the database from a tool running directly on yourown machine, you will need to expose its port (5432).

    Add a ports section to the db service in docker-compose.yml and map theport to your host:

    1. db:
    2.     image: postgres:9.4
    3.         - 5432:5432

    This means that external traffic reaching the container on port 5432 will berouted to port 5432 internally.

    The ports are <host port>:<container port> - you can choose another hostport if you are already using 5432 on your host.

    Connect to the database¶

    You will need to use the following details:

    • port: 5432
    • username: postgres
    • password: not required
    • database: db
      Access the database using your Postgres tool of choice. Note that you mustspecify the host address, 127.0.0.1.

    For example, if you’re using the psql command line tool, you can connect to the projectdatabase with:

    Note

    It’s often more appropriate to pull down the Cloud database to a localproject to interact with it there:

    1. divio project pull db live  # or test

    See the for more on using thesecommands.

    From the project’s Cloud application container¶

    Note

    SSH access to an application container on the Cloud is .

    Log into your Cloud project’s container (Test or Live) over SSH.

    Using dbshell¶

    Run:

    1. ./manage.py dbshell

    This will drop you into the psql command-line client, connected to your database.

    Connecting to the database manually¶

    You can also make the connection manually. Run env to list your environment variables. Amongstthem you’ll find DATABASE_URL, which will be in the form:

    1. DATABASE_URL=postgres://<user name>:<password>@<address>:<port>/<container>

    Access to Cloud databases other than from the associated application containers is not possible -it is restricted, for security reasons, to containers running on our own infrastruture.

    It’s beyond the scope of this article to give general guidance on using Postgres, but theseexamples will help give you an idea of some typical operations that you might undertake while usingDivio Cloud.

    All the examples assume that you are interacting with the local database, running in its dbcontainer.

    In each case, we launch the command from within the web container with docker-compose run
    —rm web     and we specify:

    • host name: -h postgres
    • user name: -U postgres

    Dump the database¶

    Dump the database to a file named database.dump:

    Drop the database¶

    Drop (delete) the database named db:

    1. docker-compose run --rm web dropdb -h postgres -U postgres db

    Create a database named db:

    1. docker-compose run --rm web createdb -h postgres -U postgres db

    Apply the hstore extension¶

    Apply the hstore extension (required on a newly-created local database) to he database nameddb:

    1. docker-compose run --rm web psql -h postgres -U postgres db -c "CREATE EXTENSION hstore"

    Restore the database¶

    Restore a database named db from a file named database.dump:

    To reset the database (with empty tables, but the schema in place) you would run the commands aboveto drop and the database, create the the hstoreextension, followed by a migration:

      Restore from a downloaded Cloud backup¶

      Untar the downloaded backup.tar file. It contains a database.dump file. Copy the file toyour local project directory, then run the commands above to drop and the database, create the the hstore extension, and then.