PostgreSQL on Docker

PostgreSQL on Docker

Even with popular management software, you always have to be prepared for a few possible challenges down the road. This is also the case with PostgreSQL and below I’ll show you how to handle a common problem users often face.

The challenge comes when you have different versions of PostgreSQL running on your computer because of different customer infrastructures. Recently I tried to restore a pg_dump of version 9.5 into a 9.3 version that’s currently on my development environment. Unfortunately, errors started popping up on my screen. Let me show you how to fix it fast.

How to Solve PostgreSQL Challenges in Docker

After you have Docker running on your local network, just take it for a spin. You can follow these easy steps.

Getting Ready

1. Firstly, run it as root

   _> sudo su

2. List all running containers

   _> docker ps # use (-a) for all containers

3. Search containers

   _> docker search postgres

4. Pull an image into your local

   _> docker pull postgres

   
   

Now you will be ready to start Docker containers, exposing PostgreSQL ports on different numbers and then your local POSTGRES instance, which is 5432.

Docker Containers

1. Start a container running PG 9.5 on port 54321

   _> docker run --name pg95  -p 54321:5432 -d postgres:9.5

   • Important notes:

   • The name of the container will be pg95
   • Port 5432 will be tunneled to host 54321
   • -d means that it will run on daemon mode

2. Now try the following:

   _> docker ps
   CONTAINER ID        IMAGE               COMMAND                CREATED             STATUS              PORTS                     NAMES
   53819ef289f4        postgres:9.5        "/docker-entrypoint.   14 hours ago        Up 7 seconds        0.0.0.0:54321->5432/tcp   pg95

3. Connect using a command line tool

   :psql
   _> psql -h localhost -p 54321 -U postgres
   postgres=# select version();
                                            version
   ------------------------------------------------------------------------------------------
    PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
   (1 row)
   

   • Now you can use the usual command line tools, such as:

   • pg_dump
   • pg_restore
   • psql
   • created
   • dropdb

     • Important note: you just need to add -h localhost and -p 54321.

You will need a user for Odoo or the server will not allow running with the POSTGRES user.

_> createuser -h localhost -p 54321 -U postgres odoo
_> dropdb -h localhost -p 54321 -U postgres database_name
_> createdb -h localhost -p 54321 -U postgres -O odoo database*name
*> pg_restore -h localhost -p 54321 -U postgres -O -d database_name ~/db/file.dump

1. Connect Odoo In order to connect Odoo to your development environment, simply add or change the following in the CONF file:

   db_user = odoo
   db_port = 54321
   db_host = localhost

Conclusion

See? It’s easier than you think and with PostgreSQL and Docker you have everything you need.

For more questions about PostgreSQL challenges, please don’t hesitate to contact our team. We’ll be happy to assist.

Request Your Free Assesment