ERROR: could not create unique index "file_pkey"

[Birgit Ducarroz]

Hi,

I have an old bareos.sql dump made with postgresql 12. It is very important, it contains our archives on tape.
I try to import it to a new postgresql 16, but I get

ERROR:  could not create unique index "file_pkey"
DETAIL:  Key (fileid)=(29987396) is duplicated.

I investigated a lot of time to delete all the duplicates - It seems that there are thousands, I don't know why. But when I try then to import the cleaned database, I see no jobs.

When I start my old bareos version with the old database, all works fine: i can backup, restore etc., so it seems that all these duplicates do not interfere anywhere.

I can play around with the sql file, no problem of data loss since I have backuped my installaton. So every suggestion is appreciated.

My question is, how can I clean my database correctly from these duplicates and finally import my dump into the new psql 16?

In case it is not possible I have a plan B, but maybe someone can help me anyway.

Thank you so lot in advance.
Birgit

[Andreas Rogge]

Hi Birgit,

Am 10.07.25 um 10:36 schrieb 'Birgit Ducarroz' via bareos-users:

> When I start my old bareos version with the old database, all works
> fine: i can backup, restore etc., so it seems that all these duplicates
> do not interfere anywhere.

this indeed looks like a problem with the database itself.
AFAICT this could only happen if there are duplicates in that primary
key column - which would indicate a corrupted database.

However, the fileid column isn't really critical unless you use BVFS.

What you could try if you still have the original database is to
pg_dump --schema-only
and
pg_dump --data-only

This way you get seperate dumps for the database structure and the data.
When you then restore the schema first, you can drop the primary key
before restoring the data.

In the end you will have the same corruption in your restored database
that you had in the original one.

> I can play around with the sql file, no problem of data loss since I
> have backuped my installaton. So every suggestion is appreciated.

You can just remove the line adding the primary key. Most of Bareos will
work fine without that primary key, even though BVFS and a few other
things will not like it.

> My question is, how can I clean my database correctly from these
> duplicates and finally import my dump into the new psql 16?

That's a question for the PostgreSQL mailing list, as your PostgreSQL 12
database should not have these duplicates in the first place.

Hope that helps!

Best Regards,
Andreas


--
Andreas Rogge andrea...@bareos.com
Bareos GmbH & Co. KG Phone: +49 221-630693-86
http://www.bareos.com

Sitz der Gesellschaft: Köln | Amtsgericht Köln: HRA 29646
Komplementär: Bareos Verwaltungs-GmbH
Geschäftsführer: Stephan Dühr, Jörg Steffens, Philipp Storz

[Birgit Ducarroz]

Hi Andreas,

Thank you so much for your answer. I first have to check a little bit and will analyse what is the best way to get working my system without problems in the future.

I think I will try this if I really can't repair the database.

Kind regards,
Birgit

[Birgit Ducarroz]

Hi,

I was able to solve the problems. 

Here is a brief description of what I did, which may help someone else in the future:

Since the non-migrated server and database were working:

On the old server:

----------------------------

- MAKE A BACKUP OF THE DATABASE.

- CLEAN UP THE DATABASE:

   /usr/sbin/bareos-dbcheck -u bareos -d bareos -f (wait .....)

- LOG INTO THE DATABASE AND CLEAN UP

  VACUUM VERBOSE ANALYZE file;

- EXTRACT THE DATABASE SCHEMA:
   postgres=# \! pg_dump --schema-only --username=postgres --dbname=bareos > /<path>/schema_only.sql

On the new server:

------------------------------

- INSTALL THE OLD POSTGRES VERSION - IF YOU NEED IT TEMPORARLY
- INSTALL THE NEW POSTGRES VERSION
- CREATE THE NEW DB
  sudo pg_createcluster 16 main --datadir=/your-path/database

- ADJUST PORT IF NECESSARY:
   vi /etc/postgresql/16/main/postgresql.conf
   port = 5432

- START THE DB, CREATE USER AND TABLESPACE:
sudo -u postgres psql -p 5432 -c “CREATE ROLE bareos LOGIN;”
sudo -u postgres psql -c “CREATE TABLESPACE bareos_ts LOCATION ‘/your-path/database’;”

- SET THE CORRECT ENCODING:
sudo -u postgres createdb bareos \
  -O bareos \
  -E SQL_ASCII \
  -T template0 \
  --lc-collate=C \
  --lc-ctype=C

- CHECK ENCODING:
sudo -u postgres psql -c “\l bareos”

Example Output:

 List of databases
  Name  | Owner  | Encoding  | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
--------+--------+-----------+-----------------+---------+-------+------------+-----------+-------------------
 bareos | bareos | SQL_ASCII | libc            | C       | C     |            |           |
(1 row)

- CREATE BAREOS USER, SET PASSWORD:
sudo -u postgres psql -p 5432 -c "CREATE DATABASE bareos OWNER bareos;"

sudo -u postgres psql -c "ALTER USER bareos WITH PASSWORD 'yourpassword';"

sudo -u bareos psql -c "ALTER USER bareos WITH PASSWORD 'yourpassword';"

- CHECK ENCODING AGAIN
sudo -u postgres psql -d bareos -c "SHOW server_encoding;"

 server_encoding
-----------------
 SQL_ASCII
(1 row)

- IMPORT THE ADJUSTED DATABASE:
sudo -u postgres psql -d bareos -f /your-path/bareos.sql


- UPDATE THE DATABASE:
sudo -u postgres /usr/lib/bareos/scripts/update_bareos_tables

- STOP THE OLD DATABASE AND START THE NEW DATABASE:
sudo systemctl stop postgresql
sudo pg_ctlcluster 12 main stop
sudo pg_ctlcluster 16 main stop

If problems arise, delete the new database and start again from the beginning:
sudo pg_dropcluster 16 main –stop

- START THE NEW DATABASE:
sudo pg_ctlcluster 16 main start