error during postgresql db backup restoration

Question

A database's backup file created in Windows 7 with:

pg_dump -U postgres -Fc [db_name] >D:\[db_backup_file].sql

Then I dropped it and restored it to test the process with:

pg_restore -U postgres -C -d postgres D:\[db_backup_file].sql

Everything worked fine.

However as I tried to restore it in Ubuntu 20.04 in a different device, I got an error: could not execute query: ERROR: invalid locale name: (same as here)

So I followed the given instructions creating the database,

sudo -u postgres psql
create database [db_name];

and then I placed in the terminal the following command to restore backup:

pg_restore -U postgres -d postgres /home/../../[db_backup_file].sql

But again I got errors, as many were the tables, multiplied by four. So for every table I get the following errors:

pg_restore: from TOC entry 315; 1259 29971 TABLE [table_name] postgres
pg_restore: error: could not execute query: ERROR:  relation [table_name] already exists
Command was: CREATE TABLE public.[table_name] (
    [pkey_column_name] integer NOT NULL,
    .......
    .......
    .......
    .......
    .......
    .......
);


pg_restore: from TOC entry 314; 1259 29969 SEQUENCE [table_name]_[pkey_column_name]_seq postgres
pg_restore: error: could not execute query: ERROR:  relation "[table_name]_[pkey_column_name]_seq" already  
exists
Command was: CREATE SEQUENCE public.[table_name]_[pkey_column_name]_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


pg_restore: from TOC entry 3522; 0 29971 TABLE DATA [table_name] postgres
pg_restore: error: COPY failed for table "[table_name]": ERROR:  duplicate key value violates unique constraint  "[table_name]_pkey"
DETAIL:  Key ([pkey_column_name])=(1) already exists.
CONTEXT:  COPY [table_name], line 1


pg_restore: from TOC entry 3267; 2606 29976 CONSTRAINT [table_name] [table_name]_pkey postgres
pg_restore: error:  
could not execute query: ERROR:  multiple primary keys for table "[table_name]" are not allowed
Command was: ALTER TABLE ONLY public.[table_name]
    ADD CONSTRAINT [table_name]_pkey PRIMARY KEY ([pkey_column_name]);

When the tables were created the primary keys - if something has to do with it - were defined as auto increment, in the form of:

CREATE TABLE [table_name] (
    [pkey_column_name] serial primary key,
    .......
    .......
    .......
    .......
    .......
    .......
);

Can anyone please help me about that?

EDIT: Actually the missing codepage type in the first error in my post yesterday was "Greek_Greece.1253". I used the locale -a command as you said, and I saw that among others my Ubuntu OS has en_US.UTF-8 and el_GR.UTF-8. So I'm wondering if the issue could be that incompatibility between Windows and Ubuntu character sets. If yes, how you thing that I could manage it? Luckily the windows 7 device from which the backup files come from is still in use, and so the databases are active. But what I tried to create again the databases giving for LC_COLLATE and LC_CTYPE values compatible with ubuntu, didn't work.

EDIT 2: Finally it was that windows-linux incompatibility in characters encoding. When I tried to use en_US.UTF-8 or el_GR.UTF-8 with encoding parameter as following:

pg_dump -E en_US.UTF-8 -U postgres -Fc [db_name] > D:\[backup_file].sql

I was getting:

pg_dump: invalid client encoding "en_US.UTF-8" specified

Then I tried to create in ubuntu the db before I restore it, under the command:

CREATE DATABASE database_name WITH ENCODING 'utf8' LC_COLLATE='el_GR.utf8' LC_CTYPE='el_GR.utf8' TEMPLATE template0;

and then:

pg_restore -U postgres -d postgres ~/../../backup_file.sql

but I got the same batch of errors I had in the initial post.

So the solution was to create a new database in windows, but now under 'C' char encoding (POSIX wouldn't be accepted), copy the tables from one database to another:

pg_dump -U postgres -t [table_name] [database_name] | psql -U postgres -d [database_name]

and then dump the newly created db, and restore it in ubuntu environment.

bfris · Accepted Answer · 2020-06-08T15:50:01.873

1

It could be that your Ubuntu OS does not have an en_US.UTF-8 locale. You can check for this by using this command in terminal:

locale -a  # list all locales known to OS

If you cannot find the locale in the list, try making a new one according to this post

EDIT With the additional information that the Windows encoding is Greek_Greece.1253, it still sounds like there is a mismatch. According to the pg_dump docs, you can explicitly set the encoding using the -E option. Probably you want to set it to something that Ubuntu can handle (i.e. en_US.UTF-8 or el_GR.UTF-8)

-E encoding
--encoding=encoding

    Create the dump in the specified character set encoding. By default, the dump is
    created in the database encoding. (Another way to get the same result is to set the 
    PGCLIENTENCODING environment variable to the desired dump encoding.)

edited Jun 08 '20 at 15:50

answered Jun 06 '20 at 17:57

bfris

5,272
1
20
37

Thank you for your response. I've placed an 'edit' in my post about that and about some other details. – george lal Jun 07 '20 at 22:49
Well, in my current project the database's tables could be reproduced if needed by an internet source, so my intention to find a solution had to do mostly with past projects' databases, and it wasn't kind of urgent to come up with the solution. The solution's details are in a second edit in my post. Thanks again. – george lal Jun 27 '20 at 11:00

error during postgresql db backup restoration

1 Answers1