Problems importing MB Dump files into PostgreSQL

MusicBrainz Development
1

I am interested in querying this database on my local instance of PostgreSQL (admittedly I have never used PostgreSQL before) - The problem is when I try to import the data dump (lets say I want to import the ‘artist_alias’ table), I am getting tons of errors. Here is the command I am attempting to use:

psql database_name_I_created < artist_alias

When I try that I get tons of errors such as:

invalid command \N

Also I get the following:

ERROR: syntax error at or near “&”
LINE 1: & The Fallen Angels

After loading the file into Vi it is immediately obvious that the varchar strings in the dump file have not been qualified with quotes.

A) How am I supposed to get this data into my database
B) There is repeated use of the word ‘metadata’ on the website. Is this the real data? I.E. actual artists, record names, song names, etc. Or is this some kind of metadata used to query the real data?

Also I tried the following instructions:

https://wiki.musicbrainz.org/History:Database_Installation

and step 4 - where it instructs to use the following command:

mbrainz$ for t in * ; do echo date $t ; echo “\copy $t from ./$t” |
psql -U musicbrainz_user importtest && mv $t …/done/ ; done ; echo date Done

Does not work - it just gives me this:

postgres@mpax:~/mbdump_full$ command here
‘>’

It looks like there is a terminator from the command that is incomplete but I cannot figure out what that is.

Ultimately I’m looking for the real data. The actual artist names, song titles, publish dates. I think what is making this challenging is that I am still not fully understanding the overall architecture of the data or the server or even the MB site.

So, any assistance in that regard that you guys could provide would be super helpful!

Thanks

2

A quick guess is you are not running this as the postgres user and not authenticated with the database.
If you run: psql musicbrainz do you get a database prompt or is it prompting you for a username and password.

On linux there is usually a postgres user that owns the database.
When this user runs programs that connect to the database it is automatically authenticated without needing to prompt the user for a password.
You can configure other users to be able to authenticate with thair operating system user, let me know and I can give you notes.

I have configured my database using the full musicbrainz server instead of mbslave so I am not sure if it produces different results.

3

You need to use musicbrainz-server to import the database (or possibly mbslave). Follow the documentation; in the case of MBS, INSTALL.md.

4

Hello - Yes I am using the ‘postgres’ user that the install provided. Do I need to install the server to import the data into postgreSQL?

5

You have a few options in setting up a database.

  • use mbslave to set up a database containing just the data.
  • use musicbrainz server to set up a database plus the web server software used for the website.
  • Download and run musicbrainz vm in virtualbox, this has the full server installed and a database populated.

All 3 should give you a database with the same tables that can be kept up to date with the latest changes.
Personally I run the full server but you may not want that.

1 Like
6

Ok great - I will try using the mbslave option then. Still I cannot understand why a simple import of the data dump will not work - I guess this will end up being a matter of intellectual curiosity - i.e. I wonder what I’m doing wrong?

1 Like
7

This is not a pg_dump dump, as you still seem to assume.

Furthermore, following instructions from a wiki page in the History: namespace is not a good idea (it also says so at the top).

8

Then it looks like I don’t know the difference in “dump types” for PostgreSQL - I am brand new to the database and really am only using it to get this data so I can then dump it out to a different DB of choice.

The link - looks like I missed the warning at the top - a bit of trial and mostly error on my part. Worth a shot is a phrase I mutter frequently. I get mixed results. Eventually I score. This is part of the process - and I appreciate the help I’ve received here.

Cheers.

9

dns_server - thank you for the link to mbslave - I think I was overwhelmed with information initially and did not see or understand that this is exactly what I wanted. I have imported all the data and am happily exploring the mb database.

Thank you very much!

1 Like
10

Also - is there a place where there are some recommended common queries listed and maybe a diagram showing the relations / keys to each table? I realize this is probably what the server/website is for but for my end purposes I won’t be using the data that way

11

https://musicbrainz.org/doc/MusicBrainz_Database/Schema

12

If you’d like to play with some queries, you can look at the reports. If you see one that looks interesting, you can find the query that generated it @ MusicBrainz::Server::Report.

If it helps, here’s the full list of tables.

13

psql is the command line interface to PostgreSQL and can be quite tricky.

The official graphical user interface for PostgreSQL (GUI) if pgAdmin3:
https://www.pgadmin.org/

Enable logging server side in PostgreSQL:
https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html

Run to the query using pgAdmin3 to see what is going on.