Installation Problems

[thomase1993]

Hi,

I tried to install your software with a postgresql server. The db user has no create database rights. That's why I imported the schema of goatcounter db schema-pgsql to postgresql with 'sudo -u postgres psql -U goat -W -d goat < /tmp/schema.sql'

After that I was a bit lost if I had to create a site or just serve it. But I finally tried the db migration with goatcounter db migrate all -db 'postgresql://goat:dummy@1.1.1.1/goat?sslmode=disable' and got an error message:

goatcounter: zdb.Migrate.Run: running "2021-11-15-1-user-role": pq: constraint "users_role_check" of relation "users" does not exist

goat=> \d users
                                             Table "public.users"
     Column      |            Type             | Collation | Nullable |                Default
-----------------+-----------------------------+-----------+----------+----------------------------------------
 user_id         | integer                     |           | not null | nextval('users_user_id_seq'::regclass)
 site_id         | integer                     |           | not null |
 email           | character varying           |           | not null |
 email_verified  | integer                     |           | not null | 0
 password        | bytea                       |           |          |
 totp_enabled    | integer                     |           | not null | 0
 totp_secret     | bytea                       |           |          |
 role            | character varying           |           | not null | ''::character varying
 access          | jsonb                       |           | not null | '{"all": "a"}'::jsonb
 login_at        | timestamp without time zone |           |          |
 login_request   | character varying           |           |          |
 login_token     | character varying           |           |          |
 csrf_token      | character varying           |           |          |
 email_token     | character varying           |           |          |
 seen_updates_at | timestamp without time zone |           | not null | CURRENT_TIMESTAMP
 reset_at        | timestamp without time zone |           |          |
 settings        | jsonb                       |           | not null | '{}'::jsonb
 created_at      | timestamp without time zone |           | not null |
 updated_at      | timestamp without time zone |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (user_id)
    "users#site_id" btree (site_id)
    "users#site_id#email" UNIQUE, btree (site_id, lower(email::text))
Check constraints:
    "users_email_check" CHECK (length(email::text) > 5 AND length(email::text) <= 255)
Foreign-key constraints:
    "users_site_id_fkey" FOREIGN KEY (site_id) REFERENCES sites(site_id) ON UPDATE RESTRICT ON DELETE RESTRICT
Referenced by:
    TABLE "api_tokens" CONSTRAINT "api_tokens_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE RESTRICT ON DELETE RESTRICT

I use the goatcounter-v2.1.0-linux-amd64 release.

There is maybe an other bug or an outdated readme.md in the postgresql section:
goatcounter serve -db 'postgresql+host=a dbname=b user=c password=d sslmode=disable' -listen :8081 -tls none

gives the error:

goatcounter: zdb.Connect: zdb.Connect: unrecognized database engine "" in connect string "postgresql+host=a dbname=b user=c password=d sslmode=disable"

I think the readme is outdated because I only find this syntax here: https://github.com/arp242/goatcounter#postgresql . Are you interested in a setup guide with step by step instructions in a pull request?

[arp242]

The README is actually newdated (is that a word? It is now!) and applies to the current master, where I changed the -db syntax. The old syntax is with :// instead of + (e.g. 'postgresql://host=a dbname=b user=c password=d sslmode=disable').

You can view the readme for 2.1 over here: https://github.com/arp242/goatcounter/tree/release-2.1 – I should probably make the warning about that a bit more prominent.

As for the migration: yeah, that doesn't seem to work 😅 That line seems to be missing from the schema; you can add it manually with: insert into version values ('2021-11-15-1-user-role').

I'll create a 2.1.1 tomorrow which fixes it.