I have successfully set up a local MusicBrainz server with all the data, thanks to the excellent musicbrainz-docker and instructions. A local copy of the server software now appears at http://localhost:5000 . But what I would really like to do is run raw SQL queries on my local copy of the database.
I am guessing this is possible. The MusicBrainz server is probably making calls from the musicbrainz-1 container to the db-1 container. But I don’t know Docker well, so I’m not familiar with the commands to do the same thing from my computer’s command line.
Can anyone give me a simple formula to do this?
I tried connecting to port 5432 on my localhost, but there was no postgresql server there:
% psql -U postgres -h localhost -p 5432
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
After fumbling in the dark a bit, this gave me interactive access to my copy of the MB database:
docker exec -it musicbrainz-docker-db-1 psql -U musicbrainz -d musicbrainz_db
For example,
% docker exec -it musicbrainz-docker-db-1 psql -U musicbrainz -d musicbrainz_db
psql (16.3 (Debian 16.3-1.pgdg120+1))
Type "help" for help.
musicbrainz_db=# \conninfo
You are connected to database "musicbrainz_db" as user "musicbrainz" via socket in "/var/run/postgresql" at port "5432".
musicbrainz_db=# \d
musicbrainz_db=# \d artist
Table "musicbrainz.artist"
Column | Type | Collation | Nullable | Default
------------------+--------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('artist_id_seq'::regclass)
gid | uuid | | not null |
name | character varying | | not null |
sort_name | character varying | | not null |
begin_date_year | smallint | | |
begin_date_month | smallint | | |
begin_date_day | smallint | | |
end_date_year | smallint | | |
end_date_month | smallint | | |
end_date_day | smallint | | |
type | integer | | |
area | integer | | |
gender | integer | | |
comment | character varying(255) | | not null | ''::character varying
edits_pending | integer | | not null | 0
last_updated | timestamp with time zone | | | now()
ended | boolean | | not null | false
begin_area | integer | | |
end_area | integer | | |
Indexes:
"artist_pkey" PRIMARY KEY, btree (id)
"artist_idx_area" btree (area)
"artist_idx_begin_area" btree (begin_area)
"artist_idx_end_area" btree (end_area)
"artist_idx_gid" UNIQUE, btree (gid)
"artist_idx_lower_unaccent_name_comment" btree (lower(musicbrainz_unaccent(name::text)), lower(musicbrainz_unaccent(comment::text)))
"artist_idx_musicbrainz_collate" btree (name COLLATE musicbrainz)
"artist_idx_name" btree (name)
"artist_idx_null_comment" UNIQUE, btree (name) WHERE comment IS NULL
"artist_idx_sort_name" btree (sort_name)
"artist_idx_txt" gin (mb_simple_tsvector(name::text))
"artist_idx_txt_sort" gin (mb_simple_tsvector(sort_name::text))
"artist_idx_uniq_name_comment" UNIQUE, btree (name, comment) WHERE comment IS NOT NULL
Check constraints:
"artist_edits_pending_check" CHECK (edits_pending >= 0)
"artist_ended_check" CHECK ((end_date_year IS NOT NULL OR end_date_month IS NOT NULL OR end_date_day IS NOT NULL) AND ended = true OR end_date_year IS NULL AND end_date_month IS NULL AND end_date_day IS NULL)
musicbrainz=# exit
One thing I will want to do is save the results of queries on my client. I had hopes for this approach:
docker exec -it musicbrainz-docker-db-1 psql -U musicbrainz -d musicbrainz_db --csv --command=‘(some command)’
It succeeds for very simple queries, but seems to hang after about 20-25 rows of output for longer queries. For instance, I only get about 23 rows of output from:
docker exec -it musicbrainz-docker-db-1 psql -U musicbrainz -d musicbrainz_db
--csv --command='select RGN.N_Releases, count(RGN.N_Releases) as N_ReleaseGroups
FROM (select RG.id as RG_ID, count(*) as N_Releases from release_group as RG
inner join release as R on R.release_group = RG.id group by RG.id
order by N_Releases ASC) as RGN group by RGN.N_Releases
order by RGN.N_Releases ASC;' > $HOME/Documents/myfile.csv
In order to have the DB available on your local machine you need to expose the port. The musicbrainz-docker repository contains some docker-compose overrides to enable this, see the section about compose overrides in the README. Specifically and says to use this command to enable the DB port:
admin/configure add publishing-db-port
docker compose up -d
With this you should be able to access postgres on localhost.
Ah! Thank you for the tip. I will try that.
I saw that sentence. But what I read is:
If you are running a database only mirror, run this instead:
That sounds to me like a statement about who should use this command, not a description of what this command does. I am not running a database only mirror.
Maybe it would be clearer if it said something like, “To publish the port only of the database service (for instance, if you are running a database only mirror, or only want access to the database), run this:” .
Also, the structure of that section threw me off. It starts with a discussion of vulnerabilities in the search service, which isn’t of interest to me. That made me think that nothing in the section applied. Maybe it would be clearer if that paragraph were moved to the end of the section.
That will only expose the port of the db. If you are running only the database you cannot use the other command to expose ports for all services, as those are not available.
But if your run the full setup you can of course expose the database port.