Connecting via PostgreSQL Protocol
Running YDB with PostgreSQL compatibility enabled
Currently, the PostgreSQL compatibility feature is available for testing in the Docker image: ghcr.io/ydb-platform/local-ydb:nightly.
Commands for starting a local Docker container with YDB and open ports for PostgreSQL and Embedded UI:
Tip
In this example, the containers are intentionally created in a way that their state is deleted after they stop. This simplifies the instructions and allows you to repeatedly run tests in a known environment without worrying about past test failures.
To preserve the container's state, you need to remove the environment variable YDB_USE_IN_MEMORY_PDISKS.
To launch using a Docker Compose configuration file, it must already be installed on your system.
docker-compose.yaml:
services:
ydb:
image: ghcr.io/ydb-platform/local-ydb:nightly
ports:
- "5432:5432"
- "8765:8765"
environment:
- "YDB_USE_IN_MEMORY_PDISKS=true"
- "POSTGRES_USER=${YDB_PG_USER:-root}"
- "POSTGRES_PASSWORD=${YDB_PG_PASSWORD:-1234}"
- "YDB_EXPERIMENTAL_PG=1"
Run:
docker-compose up -d --pull=always
docker run --name ydb-postgres -d --pull always -p 5432:5432 -p 8765:8765 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=1234 -e YDB_EXPERIMENTAL_PG=1 -e YDB_USE_IN_MEMORY_PDISKS=true ghcr.io/ydb-platform/local-ydb:nightly
After launching the container, you can connect to it via PostgreSQL clients on port 5432, the database local, or open the web interface on port 8765.
Connecting to the Running Container via psql
Upon executing this command, the interactive command-line interface of psql, the PostgreSQL client, will be launched. All subsequent queries should be entered within this client interface.
docker run --rm -it --network=host postgres:14 psql postgresql://root:1234@localhost:5432/local
Hello world example
SELECT 'Hello, world!';
Output:
column0
---------------
Hello, world!
(1 row)
Creating a Table
The primary purpose of database management systems is to store data for later retrieval. As an SQL-based system, the principal abstraction for storing data is the table. To create our first table, execute the following query:
CREATE TABLE example
(
key int4,
value text,
PRIMARY KEY (key)
);
Adding test data
Now let's populate our table with some initial data. The simplest way to do this is by using literals.
INSERT INTO example (key, value)
VALUES (123, 'hello'),
(321, 'world');
Querying test data
SELECT COUNT(*) FROM example;
Output:
column0
---------
2
(1 row)
Stopping the Container
This command will stop the running container and delete all data stored within it.
In the directory containing the docker-compose.yaml file, execute the command that will stop the container and remove its data:
docker-compose down -vt 1
Note
To stop a Docker container and preserve its data, you should run it without the YDB_USE_IN_MEMORY_PDISKS environment variable and use the stop command:
docker-compose stop
This command will stop the container and remove the data:
docker rm -f ydb-postgres