Docker & PostgreSQL - postgres y DBeaver

Name: Docker & PostgreSQL - postgres y DBeaver
Uploaded: 2022-05-27T21:50:04.000Z
Duration: 46 min 41 s

How to Interact with PostgreSQL Container Using Docker

Introduction to PostgreSQL and Docker

The speaker introduces the concept of using a PostgreSQL container via Docker, emphasizing its utility for development without needing to install PostgreSQL directly on the computer.

The video aims to teach viewers how to install PostgreSQL in a Docker container, including commands, environment variables, and where to find additional information.

Prerequisites for Installation

Viewers are advised to have Docker installed; links to installation guides for Linux, Mac, and Windows will be provided in the description.

The speaker notes that installing Docker on Windows is straightforward due to an installer, while Linux requires executing specific commands.

Running Docker and Downloading PostgreSQL

After installation, users should run Docker. The speaker demonstrates opening a console window (cmd) on Windows.

To download the PostgreSQL container, users need to search for "Docker PostgreSQL" documentation online.

Accessing Documentation and Downloading Container

The official site provides access to various containers including databases like PostgreSQL. Users can find both summarized and complete documentation.

To download the container, the command docker pull postgres is used. This command fetches the latest version of the official PostgreSQL image.

Executing and Configuring PostgreSQL Container

After downloading, users must execute the container using docker run postgres, but they need to set up necessary environment variables first.

An error may occur if required variables such as POSTGRES_PASSWORD are not set; this variable is essential for initializing the database.

Setting Environment Variables

Users learn how to pass environment variables when running containers. For example: --env POSTGRES_PASSWORD=mysecretpassword.

Additional optional environment variables include creating a user or database at startup by referencing documentation for their names.

Final Steps in Execution

The speaker explains how setting up these configurations allows successful execution of a new instance of a PostgreSQL database within Docker.

Connecting to a Docker Container

Initial Setup and Connection

The speaker discusses connecting to an interactive Docker container, emphasizing the need to identify the container's name.

They execute docker ps to view running processes and containers, noting that a specific container was created 41 seconds prior, running on port 5432.

The speaker demonstrates how to connect to the container using its name, copying it directly into the command line for ease of access.

Accessing PostgreSQL within the Container

Once connected, they use PostgreSQL commands, starting with psql -U postgres, where "postgres" is the default user created during installation.

The password is provided using --password, allowing access to create databases and run queries like CREATE DATABASE.

A demonstration of executing SQL queries is shown; for example, performing a simple addition query (SELECT 1 + 1).

Exiting and Managing Containers

To exit from the PostgreSQL prompt, they suggest using Ctrl + D or Ctrl + C while noting that the container remains active in another console.

Recreating Docker Containers with Parameters

Stopping and Removing Existing Containers

The speaker explains issues with stopping containers if other processes are running. They recommend restarting CMD if necessary.

Before recreating a new container, existing ones must be removed. They demonstrate checking for running containers with docker ps and listing all containers with docker ps -a.

Deleting Containers

To delete containers, they use docker rm <container_name>, showing how to remove both previously created containers effectively.

Creating New Containers with Custom Parameters

After confirming no active containers remain, they prepare to recreate a PostgreSQL container but will include additional parameters such as initial database settings.

Setting Up User Credentials in Docker

Specifying User and Database at Creation

When creating a new instance of PostgreSQL in Docker, users can specify credentials like username (POSTGRES_USER) alongside setting up an initial password (POSTGRES_PASSWORD).

An example is given where "fast" is set as a custom username while maintaining "password" as the password for accessing PostgreSQL.

Customizing Initial Database Creation

Setting Up Environment Variables and Running Containers

Configuring Environment Variables

The speaker discusses the importance of setting environment variables, mentioning a specific variable related to database configuration. They suggest consulting documentation for the exact name of this variable.

Executing Commands in PostgreSQL

When executing commands, the console may become unresponsive. The speaker introduces a command called -d (detached mode), allowing processes to run in the background while closing the console.

Managing Docker Containers

After creating a container, users can check its status using docker ps -l. This command displays running containers along with their IDs and names.

Users are encouraged to assign custom names to containers instead of relying on auto-generated ones. The process involves stopping and removing existing containers before recreating them with new names.

Creating and Interacting with Containers

To create a new container, users can utilize the --name parameter for easier identification. The speaker demonstrates naming conventions for clarity.

Once inside a container, users can execute interactive commands as if they were operating within a Linux virtual machine, utilizing shortcuts like Ctrl + L to clear the screen.

Connecting to Databases

The speaker explains how to connect to PostgreSQL databases from within Docker containers by specifying user credentials and database names through prompts rather than command-line parameters.

A demonstration is provided on checking current user information within PostgreSQL using SQL commands like SELECT current_user;, emphasizing proper syntax including semicolons.

Exposing Container Ports

To interact with applications outside of Docker containers, it’s necessary to expose ports. This allows external access from client applications or other services.

The speaker highlights that when re-running containers, users should include port exposure parameters (-p) alongside other options during execution for accessibility from host machines.

How to Connect to PostgreSQL Using Docker

Setting Up PostgreSQL in a Docker Container

The speaker demonstrates how to set up a PostgreSQL container named "postres" with the default password "password" and exposes port 5432 internally.

To access the internal port from outside the container, the command -p is used, mapping internal port 5432 to an external port (e.g., 5482).

Users can modify the external port number if desired; however, they choose to keep it as 5432 for simplicity.

Installing SQL Client Software

The speaker introduces 'DBeaver', a SQL client that allows connections to both local and remote SQL instances.

Installation of DBeaver is straightforward; users can select their preferred language during setup.

Connecting DBeaver to PostgreSQL

After installation, users open DBeaver and are prompted for connection settings. They select the "postres" database.

Connection details include specifying the database name ("mindfulness tv"), username ("faz"), and password ("password"). A successful connection test confirms connectivity.

Interacting with Databases

Once connected, users can create tables within DBeaver. For example, creating a table named "users".

The speaker demonstrates writing SQL commands directly in DBeaver's interface, such as creating a table using CREATE TABLE users.

Accessing Database via Command Line

The speaker shows how to interact with the PostgreSQL container through command line by entering into interactive mode.

Inside the container, they connect using psql with user credentials. An initial error indicates that no database exists until corrected by specifying "my faz debe".

Data Manipulation in PostgreSQL

After successfully connecting, they explore existing tables like "users" and demonstrate inserting data into these tables.

How to Manage Docker Containers and Images

Stopping and Removing Docker Containers

The speaker explains how to stop a running database container when development is complete, emphasizing the ability to remove it if no further modifications are needed.

To remove a container, the command docker stop is used first, followed by docker rm to delete the specified container.

The process of removing images is also discussed; using docker rmi, one can delete an image that is no longer required after pulling it initially.

Benefits of Using Docker for Database Management

The speaker highlights the efficiency of using containers over traditional installations like MySQL or PostgreSQL, noting that this method avoids unnecessary installations on each machine.