Trying to access MB database through pgAdmin and VirtualBox

Since I can’t even connect to localhost:5000 nor ssh with the last beta1 VM, I went back with the 2015-08-06 release.
After modifying hba_conf and postgresql.conf, I can connect to localhost:5000 and ssh the VM.
But I still can’t connect to the database through pgAdmin, which asks for a password when I’m trying to connect to localhost with postgres user and postgres database (despite I set trust option in pg_hba.con, and not md5).

PS : when typing “psql -h localhost -U postgres”, I get :
psql (9.3.9)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)

Hi Rob,
I’ve been reading this thread with some interest as I am trying to get the latest version of MusicBrainz working. For info all I want to do is to run some sql queries against the MB database.

So far I have installed VirtualBox and have imported the appliance musicbrainz-server-2016-12-20.ova.

I’ve got to the bit now where I enter psql -h localhost -U musicbrainz -p 15432
The response I get back is as follows:-
The program ‘psql’ can be found in the following packages:

  • postgresql-client-common
  • postgres-xc-client
    Ask your administrator to install one of them

My hopefully simple question is how do I install one of them, what is the command I should issue.
Thanks for you help

sudo apt-get install postgresql-client-common

should do the trick.

1 Like

Thanks Rob, indeed it has done the trick, all installed fine I think. I got the following back:-

sudo apt-get install postgresql-client-common
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following packages were automatically installed and are no longer required:
acl at-spi2-core colord dconf-gsettings-backend dconf-service fontconfig
fontconfig-config fonts-dejavu-core hicolor-icon-theme libasound2
libasound2-data libatk-bridge2.0-0 libatk1.0-0 libatk1.0-data libatspi2.0-0
libavahi-client3 libavahi-common-data libavahi-common3 libcairo-gobject2
libcairo2 libcanberra-gtk3-0 libcanberra-gtk3-module libcanberra0 libcolord1
libcolorhug1 libcups2 libdatrie1 libdconf1 libdrm-intel1 libdrm-nouveau2
libdrm-radeon1 libexif12 libfontconfig1 libfontenc1 libgd3
libgdk-pixbuf2.0-0 libgdk-pixbuf2.0-common libgl1-mesa-dri libgl1-mesa-glx
libglapi-mesa libgphoto2-6 libgphoto2-l10n libgphoto2-port10 libgraphite2-3
libgtk-3-0 libgtk-3-bin libgtk-3-common libgudev-1.0-0 libgusb2
libharfbuzz0b libice6 libieee1284-3 libjasper1 libjbig0 libjpeg-turbo8
libjpeg8 liblcms2-2 libllvm3.4 libnotify-bin libnotify4 libogg0
libpango-1.0-0 libpangocairo-1.0-0 libpangoft2-1.0-0 libpciaccess0
libpixman-1-0 libsane libsane-common libsm6 libtdb1 libthai-data libthai0
libtiff5 libtxc-dxtn-s2tc0 libv4l-0 libv4lconvert0 libvorbis0a
libvorbisfile3 libvpx1 libwayland-client0 libwayland-cursor0 libx11-xcb1
libxaw7 libxcb-dri2-0 libxcb-dri3-0 libxcb-glx0 libxcb-present0
libxcb-render0 libxcb-shm0 libxcb-sync1 libxcomposite1 libxcursor1
libxdamage1 libxfixes3 libxfont1 libxi6 libxinerama1 libxkbcommon0
libxkbfile1 libxmu6 libxpm4 libxrandr2 libxrender1 libxshmfence1 libxt6
libxtst6 libxxf86vm1 notification-daemon sound-theme-freedesktop x11-common
x11-xkb-utils xfonts-base xfonts-encodings xfonts-utils xserver-common
xserver-xorg-core
Use ‘apt-get autoremove’ to remove them.
The following NEW packages will be installed:
postgresql-client-common
0 upgraded, 1 newly installed, 0 to remove and 6 not upgraded.
Need to get 25.4 kB of archives.
After this operation, 173 kB of additional disk space will be used.
Get:1 http://archive.ubuntu.com/ubuntu/ trusty-updates/main postgresql-client-common all 154ubuntu1 [25.4 kB]
Fetched 25.4 kB in 0s (843 kB/s)
Selecting previously unselected package postgresql-client-common.
(Reading database … 102388 files and directories currently installed.)
Preparing to unpack …/postgresql-client-common_154ubuntu1_all.deb …
Unpacking postgresql-client-common (154ubuntu1) …
Processing triggers for man-db (2.6.7.1-1ubuntu1) …
Setting up postgresql-client-common (154ubuntu1) …

When I now issue the command psql -h localhost -U musicbrainz -p 15432 I get the following:-
Error: You must install at least one postgresql-client- package.
Sorry to hassle again but again what command should I issue.

Thanks again for you help.

Try installing postgresql-client (or, if that doesn’t work, postgresql-client-9.5).

1 Like

Thanks chirlu, I have now installed postgresql-client.

When I now issue the command psql -h localhost -U musicbrainz -p 15432 I get the following:-

psql: could not connect to server: Connection refused
Is the server running on host “localhost” (127.0.0.1) and accepting
TCP/IP connections on port 15432?

Any help appreciated, thanks.

Hmm … Where did you get the port number 15432 from? Try it without the -p 15432, or with -p 5432 instead.

I got the 15432 from the MusicBrainz Server / Setup page https://musicbrainz.org/doc/MusicBrainz_Server/Setup

Accessing the database

To access the main postgres database, you can do this:
psql -h localhost -U musicbrainz -p 15432

Is the ‘1’ a typo error in the instructions?

For both of your options for trying without -p 15432 or with -p 5432 I get the following:-

psql: could not connect to server: Connection refused
Is the server running on host “localhost” (127.0.0.1) and accepting
TCP/IP connections on port 5432?

Maybe stupid question: But you are running the commands from inside the VM, right?

Hi outsidecontext,

Not a stupid question but yes I am running the commands from inside the VM.

For info thse are my VirtualBox Port forwarding rules.

Also for info if I do psql --version I get 9.3.15

Finally got things working and can now access the database by doing the following:-

Enter musicbrainz password

\l (lists all databases)
\c musicbrainz_db (connects to database)

2 Likes

I had a look at this after another user asked the same question in #metabrainz and saw that the postgres port is not forwarded in docker compose. I opened a PR for it: https://github.com/metabrainz/musicbrainz-docker/pull/1 which will make the existing 15432 port forward work without needing to find the private IP of the postgres container

3 Likes

@thistimenextyear Hi, I have the same error message that you struggled. Could you explain how you get docker and ip address information in the screenshot? I would like to know if you make any changes in the setting.

Hi change000,

Sure, try doing the following:-

type in docker ps

When I do this I get a couple of lines returned. Look for the line that says the port 5432 and take a note of the container id, mine says 598d32af7efd.

type in docker inspect 598d32af7efd |pg or whatever your container id is.

You will see from the output something that says NetworkSettings, look for the line that says IPAddress. Make a note of the IPAddress, mine says 172.17.0.2

Now enter psql -h 172.17.0.2 -U musicbrainz or whatever your IPAddress is.

You will then be prompted to enter your password.

Hope this helps, if you have any problems then ping me.

Thank you. This helped.

Hi thistimenextyear, your responses in this thread got me this far, so thank you so much for all the help. I am now able to access the musicbrainz database within the VM, and run psql queries successfully.

The problem I’m having is - I don’t know how to extract the results out to my main OS so that I can use it in some analysis. I tried setting up pgadmin4 but I keep getting the error “FATAL: role “musicbrainz” does not exist”. See below for the settings I’m using in both pgAdmin4 and the virtualbox NAT settings.

Would appreciate hearing your recommendations on what I could try next… Thanks!

I can only post one image into this thread, so here’s the dropbox link to the image Virtualbox NAT settings screenshot

1 Like

Don’t forget to run the bin/reset-containers command from vagrant after you update your docker-compose.yml file!! I finally was able to figure it out and access the db from my OS. thanks all for the support.

1 Like

Probably it’s me, but could someone plase resume what I have to do to get it working? What I have to to in docker-compose.yml ?

thanks a lot!

No, you should use the regular command prompt once you have logged in with the vm. This way you can actually see progress if things are happening. To do this, once you open your cmd prompt, type “ssh -p 2222 vagrant@localhost” enter. then password

Hi thistimenextyear,

once i apply the psql -h 172.17.0.2 -U musicbrainz
it asks for a password, what would this be? or what steps do i take to create one

Thank you!