CODEWITHSUNDEEP
postgresqldocker
rota90
rota90

Reputation: 259

Importing postgres database in a docker postgres container

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?

Upvotes: 5

Views: 19601

Answers (3)

VeryNiceArgumentException
VeryNiceArgumentException

Reputation: 2066

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

Upvotes: 6

Z4-tier
Z4-tier

Reputation: 7998

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:

  • When you connect, are you connecting to the right database? If you are using 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.

  • Are you specifying the right schema? \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>
...

Upvotes: 0

Adiii
Adiii

Reputation: 60164

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.

postgres-api

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

Upvotes: 7

Related Questions