Tag Archives: postgres

Install PGAdmin Server With Docker

Posted on by
Reply

You can get PGAdmin 4 running in server mode with docker very easily.  Using this command will set up the server, set it to always restart in response to reboots or errors, and it will ensure that its data (users, config) is persisted between container runs.

docker pull dpage/pgadmin4

docker run -p 80:80 \
    --name pgadmin \
    --restart always \
    -v "/opt/pgadmin4/data:/var/lib/pgadmin" \
    -v "/opt/pgadmin4/config/servers.json:/servers.json" \
    -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \
    -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \
    -d dpage/pgadmin4

You can run this command afterward to see the details and confirm the restart policy as well if you like:

docker inspect pgadmin

 

Azure PaaS Postgres 10 Database Create + Connect Centos PSQL or DBeaver

Posted on by
Reply

Today I started using the “Azure Database for PostreSQL” PaaS service offering.  It went pretty smoothly, but connecting took a little more effort than I expected (all for good reasons!).

Creating the PostreSQL Service

You can find the creation screen in the Azure portal by pressing (+), clicking Databases, and scrolling down.

As with most things in Azure, creating the service through the portal was pretty trivial.  You basically just provide the name, region, resource group, subscription, select the size you want, specify a user + password, and you’re done!  It takes around a minute to complete with a smallish database size.

postgres-create

Connecting to the Database

We’re going to connect with DBeaver (its like SQuirreL and DBVizualizer if you haven’t heard of it).  Then we will also connect with the “psql” command line utility from Linux.  This should be pretty quick – but there are two wrenches in the works:

  1. SSL is enabled.
  2. Azure has blocked all inbound IPs by default – nothing can connect in.

Connecting with DBeaver

  • Go to your Postgres instance in the portal and view the “Overview” screen.
  • Open DBeaver, create a new Postgres connection.
  • Copy the server name from the portal into the host section of DBeaver.
  • Copy the Server Admin Login name from the portal into the user name section of DBeaver.
  • Type in your password for that Admin user.
  • Set the database as Postgres in DBeaver.
  • You can leave the port as the default 5432.
  • Now, go to driver properties on the left of DBeaver and set:
    • ssl to true
    • sslmode to require

This is shown here:

dbeaver-postgres

At this point, you’ve got all the connection details in DBeaver set up properly; but you still can’t connect.  You’ll have to go into the Azure portal, click “Connection Security”, and then create a firewall rule that allows your IP in.  You can also, alternatively, add in a pre-defined subnet you have for yourself, your company, etc.  At that point, everything on that subnet will be able to connect properly.

After this, you should be able to “Test Connection” successfully.

Connecting with PSQL from Centos 7

Assuming you opened up the firewall or subnet as noted at the end of the previous example with DBeaver, you can then just:

Install the PSQL client library:

And connect with the psql utility:

  • psql “sslmode=require host=yourhost.postgres.database.azure.com dbname=postgres user=youruser@yourhost”

Postgres Schema Creation

Posted on by
Reply

Historically, I have not worked with Postgres much. So, when I had to start using it, one of my first questions was how to create a schema, and how to use it for my new tables and such.

Creating a schema is exactly what you expect:

create schema myschema;

But using it is not quite what I expected.  Of course, you can do the standard thing when you’re managing your objects and use . like this:

create table myschema.mytable (x int);

But what if you just want:

create table mytable (x int);

to go into myschema by default?  To do this in Postgres, you have to add the schema to your search path.  By default your search path will be just set to the public schema; you can view it like this:

SHOW search_path;

You can set it to one or more schemas in reality.  The first schema your query sees a the named table in will be the one it takes it from.  The first schema in the list will be the default one for when you create new objects too.  So, if you did this:

SET search_path TO myschema;
create table mytable (x int);

Then your table would in fact be created in the “myschema” schema properly.

Docker Run Postgres, expose to Local Host

Posted on by
Reply

I needed to spin up a Postgres database for testing a new application, so I figured I’d do it via Docker to keep my system clean.

So, the plan is to develop an application on my PC / localhost (e.g. in PyCharm), but connect to the Postgres instance within the Docker container.

Getting & Running Postgres

This is actually quite trivial:

docker pull postgres
docker run --name postgres -e POSTGRES_PASSWORD=password -d -p 5432:5432 postgres

The first command pulls the image from docker-hub, and the second one runs the container and exposes the Postgres port externally (to the same numbered port) so that we can communicate with it from our local host.

Setting Up pgAdmin

The pgAdmin utility is a wonderful UI for working with Postgres.  You can download it here: https://www.pgadmin.org/download/.

In my case, I actually want to verify that connecting to Postgres works from outside of the container environment.  So, I chose to install the Windows version locally to help verify this.  If you are so inclined, they have Docker images instead so that you can install pgAmin in a container as well to keep your system clean.

Once you’ve installed pgAdmin, you can open it, go to the browser on the left panel, right click on “Servers”, and add a new one targeting “localhost” and port “5432”.

Once you do that and open it, you should hopefully be able to see monitoring statistics on it.  Then you can create a new database and work with it at will!

Persistent Data

Remember, if you delete your docker container, the data for the database will go away.  You can stop it and start it at much as you like though.  If you need to delete it and still access the data for some reason, look into using a volume in docker (this is what they’re for).