Setup a PostgreSQL connection to an already existing project in Docker

user_3643339 0
Viewed 664

I had never used PostgreSQL nor Docker before. I set up an already developed project that uses these two technologies in order to modify it.

To get the project running on my Linux (Pop!_OS 20.04) machine I was given these instructions (sorry if this is irrelevant but I don't know what is important and what is not to state my problem):

  1. Installed Docker CE and Docker Compose.
  2. Cloned the project with git and ran the commands git submodule init and git submodule update.
  3. Initialized the container with: docker-compose up -d
  4. Generated the application configuration file: ./init.sh

After all of that the app was available at http://localhost:8080/app/ and I got inside the project's directory the following subdirectories:

enter image description here

And inside dbdata:

enter image description here

Now I need to modify the DB and there's where the difficulty arose since I don't know how to set up the connection with PostgreSQL inside Docker.

In a project without Docker which uses MySQL I would

  1. Create the local project's database "dbname".
  2. Import the project's DB: mysql -u username -ppassword dbname < /path/to/dbdata.sql
  3. Connect a DB client (DBeaver in my case) to the local DB and perform the necessary modifications.

In an endeavour to do something like that with PostgeSQL, I have read that I need to

  1. Install and configure Ubuntu 20.04 serve.
  2. Install PostgreSQL.
  3. Configure Postgres “roles” to handle authentication and authorization.
  4. Create a new Database.

And then what?

How can I set up the connection in order to be able to modify the DB from DBeaver and see the changes reflected on http://localhost:8080/app/ when Docker is involved?

Do I really need an Ubuntu server?

Do I need other program than psql to connect to Postgres from the command line?

I have found many articles related to the local setup of PostgreSQL with Docker but all of them address the topic from scratch, none of them talk about how to connect to the DB of an "old" project inside Docker. I hope someone here can give directions for a newbie on what to do or recommend an article explaining from scratch how to configure PostgreSQL and then connecting to a DB in Docker. Thanks in advance.

Edit:

Here's the output of docker ps

enter image description here

docker postgresql
2 Answers

You have 2 options to get into known waters pretty fast:

Publish the postgres port on the docker host machine, install any postgres client you like on the host and connect to the database hosted in the container as you would have done this traditionally. You will use localhost:5433 to reach the DB. << Update: 5433 is the port where the postgres container is published on you host, according to the screenshot.

Another option is to add another service in your docker-compose file to host the client itself in a container.
Here's a minimal example in which I am launching two containers: the postgres and an adminer that is exposed on the host machine on port 9999.

version: '3'
services:
  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_PASSWORD: example

  adminer:
    image: adminer
    restart: always
    ports:
      - 9999:8080

then I can access the adminer at localhost:9999 (password is example):

enter image description here

Once I'm connected to my postgres through adminer, I can import and execute any SQL query I need:

enter image description here

A kind advice is to have a thorough lecture to understand how the data is persisted in a Docker context. Performance and security are also topics that you might want to add under your belt as a novice in the field better sooner than later.

user_137365250

If you're running your PostgreSQL container inside your own machine you don't need anything else to connect using a database client. That's because to the host machine, all the containers are accessible using their own subnet.

That means that if you do this:

docker inspect --format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 341164c5050f`

it will output a list of IPs that you can configure in your DBeaver to access the container instance directly.

If you're not fond of doing that (or you prefer to use cli) you can always use the psql inside the installation of the PostgreSQL container to achieve something like you described in mysql point nº2:

docker exec -i 341164c5050f bash -c 'psql -U $POSTGRES_USER' < /path/to/your/schema.sql

It's important to inform the -i, otherwise it'll not read the schema from the stdin. If you're looking for psql in the interactive mode, use -it instead.

Last but not least, you can always edit the docker-compose.yml file to export the port and connect to the instance using the public IP/loopback device.

user_65800470
Related