I am trying to import an existing database into a postgres docker container. This is how I proceed:
docker run --name pg-docker -e POSTGRES_PASSWORD=***** -d -p 5432:5432 -v BCS/postgres_data postgres
Then
docker exec -it pg-docker bash
psql -U postgres
postgres=# CREATE DATABASE myDB;
psql -U postgres myDB < BCS/mydb.sql
but when i execute the command \dt I have this error Did not find any relations.
knowing that my database has already tables.
So waht I am doing wrong?
First thing better to go with the approach that is mentioned by @LinPy.
Or better to copy at build time.
Dockerfile
FROM postgres
COPY mydb.sql /docker-entrypoint-initdb.d/
Another option, you do need to script for only DB creation.
FROM postgres
ENV POSTGRES_DB=mydb
The will create DB for you.
POSTGRES_DB
This optional environment variable can be used to define a different name for the default database that is created when the image is first started. If it is not specified, then the value of POSTGRES_USER will be used.
In the above, the Postgres entrypoint will take care of the SQL script.
Second thing, the current issue with database name, Postgress will not treat them in uppercase simply, unless you did some trick.
Postgresql treats the db name as lowercase, normalising. However, the field in the postgresapi does not replicate this behaviour, thus allowing you to create a database with Capital letters. The fix could be to warn the user that no uppercase letters are allowed in the db aname and to add in a validation rule to the API to stop a user creating such a database.
Change your command to create DB
docker exec -it pg-docker bash
psql -U postgres
postgres=# CREATE DATABASE myDB;
verfiy DB
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
mydb | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
so the import command will be
psql -U postgres mydb < BCS/mydb.sql
or
psql -d mydb -U postgres -f ab.sql
You can also have your sql scripts being redirected to the container like that:
Postgres user:
docker exec -i my-postgres-container psql -U postgres < created-db.sql
Regular user:
docker exec -i my-postgres-container psql -d my-db -U my-user < create-schema.sql
If you are sure that the database installed everything correctly, are you are still not seeing tables, there are 2 things you should double-check:
psql in a terminal, the database is specified with the -d switch. psql -h <host> -U <user> -d <dbname>
You can also change your database after you connect using the \connect <dbname> command.
\dt will show you tables, but you need to specify a schema first using set schema:postgres=# \dt
...
<no tables>
...
postgres=# set schema 'my_schema';
postgres=# \dt
...
<my tables>
...
/docker-entrypoint-initdb.d/?