דחוף, בנייה משודרגת נכשלה UniqueViolation

סיכום מבוסס בינה מלאכותית

vw_james encountered a failed Discourse upgrade due to a UniqueViolation error during migration, specifically a duplicate username constraint. Attempts to access PostgreSQL via psql failed, indicating the database service was down. merefield guided vw_james through troubleshooting: restarting the container, checking service status, and confirming PostgreSQL wasn’t running. Logs revealed a version mismatch — data initialized by PostgreSQL 13 was incompatible with the installed version 12.3. merefield suggested renaming data directories outside the container: swapping postgres_data_old to postgres_data and postgres_data to postgres_data_new, then restarting PostgreSQL inside the container. This restored the site with the old data. Jos, replying on behalf of vw_james, confirmed the fix worked. merefield noted the file rename could also be done inside the container since the path is mounted. The issue stemmed from an incomplete PostgreSQL 13 upgrade, and further steps will be needed for a clean upgrade later.

I tried to upgrade discourse and got the following message -

I, [2021-01-21T18:39:25.045454 #1]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
2021-01-21 18:39:32.724 UTC [4051] discourse@discourse LOG:  duration: 336.116 ms  statement: UPDATE users
	SET locale = 'en_GB'
	WHERE locale = 'en'
	
2021-01-21 18:39:33.373 UTC [4051] discourse@discourse ERROR:  duplicate key value violates unique constraint "index_users_on_username_lower"
2021-01-21 18:39:33.373 UTC [4051] discourse@discourse DETAIL:  Key (username_lower)=(xyz123) already exists.
2021-01-21 18:39:33.373 UTC [4051] discourse@discourse STATEMENT:  UPDATE users
	SET locale = 'en'
	WHERE locale = 'en_US'
	
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

I tried to do ./launcher enter app and su postgres -c 'psql discourse', got the following error -

psql: error: could not connect to server: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”?

Do you have any idea @merefield ? thanks!

Hey.

When an upgrade fails the container is left stopped. (Try to connect to your site via browser and you’ll see that’s the case)

So you may have simply not started the latest working container. (Pre-upgrade)

Do that with ./launcher start app then enter the container and attempt to jump into the DB CLI.

Thanks, I did restart the container, any thought? I tried ./launcher enter app anything else I can try?

ubuntu@:/var/discourse$ ./launcher start app
WARNING: Docker version 17.05.0-ce deprecated …
Nothing to do, your container has already started!

Did you get an error when entering the container?

No error, but when I did ./launcher logs app I got the following -

ok: run: redis: (pid 47) 918s
timeout: down: postgres: 1s, normally up, want up
ok: run: redis: (pid 47) 925s
timeout: down: postgres: 0s, normally up, want up
ok: run: redis: (pid 47) 933s
ok: run: postgres: (pid 1855) 0s
supervisor pid: 1847 unicorn pid: 1857
config/unicorn_launcher: line 71: kill: (1857) - No such process
config/unicorn_launcher: line 15: kill: (1857) - No such process
(1847) exiting
ok: run: redis: (pid 47) 938s
timeout: down: postgres: 0s, normally up, want up
ok: run: redis: (pid 47) 945s

What’s the result of ps -ef | grep postgres?

root 38 35 0 19:09 ? 00:00:00 runsv postgres
root 46 38 0 19:09 ? 00:00:00 svlogd /var/log/postgres
root 2573 2571 0 19:31 ? 00:00:00 sv start postgres
root 2575 2497 0 19:31 ? 00:00:00 grep postgres

Should I run it inside the container or just at the linux box?

Inside. Looks like it’s not running.

You will need to restart it manually I suspect, as postgres user.

Do you know the quick command to restart it?

You could try:

su - postgres /usr/lib/postgresql/13/bin/postmaster -D /etc/postgresql/13/main - replacing the 13 with your current version.

Which is the command advertised on the process of a healthy install.

No warranty with that though as I’ve not tested it!

No don’t bother doing this, see further down.

thanks. I have version 12, but got the following error -

su: invalid option – ‘D’
Try ‘su --help’ for more information.

-D is a legal switch.

Make sure you didn’t include the hyphen outside the code snippet?

btw, I’m wondering if your version 13 upgrade process failed.

If so you might like to take a look at and potentially post in this Topic: PostgreSQL 13 update

Yeah, I am still on version 12…

Sorry, looks like this is wrapped and managed by a higher level process.

If I kill mine, it automatically restarts.

try: sv start postgres

as root

do you mean run it inside docker container? thanks

Yes, that’s where the service is running.

Even better do this: sv restart postgres

Then check:

tail /var/log/postgres/current

Healthy situation is going to look something like:

2021-01-21 20:21:10.284 UTC [575368] LOG: database system is ready to accept connections somewhere there.

Here is the error log -

root@ip–app:/var/www/discourse# cat /var/log/postgresql/postgresql-12-main.log
2021-01-21 18:12:04.965 UTC [623] FATAL: database files are incompatible with server
2021-01-21 18:12:04.965 UTC [623] DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 12.3 (Debian 12.3-1.pgdg100+1).
pg_ctl: could not start server
Examine the log output.