Trying to access MB database through pgAdmin and VirtualBox

virtualmachine
musicbrainz-vm
Tags: #<Tag:0x00007f2a005a6708> #<Tag:0x00007f2a005a65c8>

#1

Hi,

I’m completely new to server/database gestion (i’m a statistician) and having hard time to access MB database through pgAdmin. I would like to query the database to construct my own csv dataset for statistical analysis.

I’m on OSX (el capitan), and have installed musicbrainz-server-2015-08-06.ova on virtualbox.

I have modified pg_hba.conf (host all all 0.0.0.0/0 trust AND host all all ::0/0 trust) and postgresql.conf (listen_address = ‘*’), and have those rules on virtualbox NAT network settings :

When I’m trying to connect to the server with pgAdmin4, I get this message :
“server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.”


#2

First off, you will likely want to use the more recent VM:

Whether that will help with the port setup, I don’t know, but first step should be trying with the newer VM version at least. :slight_smile:


#3

Thanks, I did that but I still have the same issue.
In fact, pg_hba.conf and postgresql.conf are not in the same path as before, so I can’t verify my virtual server is accepting connexions from host.


#4

@Rob, if you have time, can you take a look at this?


#5

What happens if you SSH into the VM and issue this command:

psql -h localhost -U postgres

?


#6

Unfortunately I can’t SSH the vm (I always get an “operation timed out” response), if I do the command directly through virtualbox terminal, I get this :
“The program ‘psql’ can be found in the following packages:
*postgresql-client-common
*postgres-xc-client
Ask your administrator to install one of them”


#7

Well, your answer was given to you. Install postgresql-client-common and try again.


#8

Thanks for your help, now I get this message :
“psql: Could not connect to server: Connection refused
Is the server running on host “localhost” (127.0.0.1) and acceping TCP/IP connections on port 5432?”


#9

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)


#10

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


#11

sudo apt-get install postgresql-client-common

should do the trick.


#12

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.


#13

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


#14

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.


#15

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


#16

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?


#17

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


#18

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


#19

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)


#20

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


Psql: Could not connect to server