EXIT CODE 1 when restore sql file in postgres

Question

I have an SQL file which is manually generated. I use the code below to create it.

conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_name_user, password=t_password)
cursor = conn.cursor()

table_name='product'  
with open("table_dump.sql","w+") as f:
    create_query ="""CREATE TABLE public.decima
                    (
                        product_name character varying(200) COLLATE pg_catalog."default",
                        product_owner character varying(20) COLLATE pg_catalog."default",
                        trigger_operation character varying(4) COLLATE pg_catalog."default",
                        name character varying(250) COLLATE pg_catalog."default" NOT NULL,
                        CONSTRAINT product_pkey PRIMARY KEY (name)
                    )

                    TABLESPACE pg_default;"""
    f.write('%s \n' % (create_query))  
    #select table query
    cursor.execute("SELECT * FROM %s" % (table_name)) 
    column_names = []
    columns_descr = cursor.description
    for c in columns_descr:
        column_names.append(c[0])
    #insert query (insert data)
    insert_prefix = 'INSERT INTO %s (%s) VALUES ' % (table_name, ', '.join(column_names))
    rows = cursor.fetchall()
    for row in rows:
        row_data = []
        for rd in row:
            if rd is None:
                row_data.append('NULL')
            elif isinstance(rd, datetime.datetime):
                row_data.append("'%s'" % (rd.strftime('%Y-%m-%d %H:%M:%S') ))
            else:
                row_data.append(repr(rd))
        f.write('%s (%s);\n' % (insert_prefix, ', '.join(row_data)))

When I used "table_dump.sql" to restore db, the exit code appeared. But I executed this file as a query, then my database still be created and work as usual. So how can to fix this problem. Thank you. This is the way I tried to restore This is the detail of error

Please show a sample of what the resulting file looks like, how you restore the file, and a literal quote of the error message from PostgreSQL. — Laurenz Albe
– Laurenz Albe, Commented Feb 18, 2021 at 3:29
@tadman What are placeholder values? Can you talk more about it? — Minh Tran
– Minh Tran, Commented Feb 18, 2021 at 3:52
@LaurenzAlbe As your suggestion I have added some pictures, hopefully it's clear enough — Minh Tran
– Minh Tran, Commented Feb 18, 2021 at 4:03
Oh, I see you're using placeholders later down and % up earlier. If you're using placeholders, note, no quotes around it are required. They may wreck your query. — tadman
– tadman, Commented Feb 18, 2021 at 4:57

jjanes · Accepted Answer · 2021-02-18 15:29:02Z

1

The restore option does not work with sql formatted files. (I don't know why PgAdmin4 offers 'sql' as an extension in the file chooser, that may be a bug). The restore option is made for non-plain file formats which pg_dump can produce.

You are using an old version of PgAdmin4, which loses the error messages from pg_restore. If you upgrade it to the latest version, you would then see the error message pg_restore: error: input file does not appear to be a valid archive

But what is the problem? You said running it as a script worked. So do that.

answered Feb 18, 2021 at 15:29

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Minh Tran Over a year ago

Actually we can run it as a script but it take more time than using restore. Thank for your answer. ((=

user17465548 · Accepted Answer · 2021-11-20 16:10:48Z

0

Choose the format as "Custom or tar" and browse the Filename in the other field. Then you can choose sql in the browse window. Maybe you choose Format as "directory". That was my mistake for the same error.

answered Nov 20, 2021 at 16:10

user17465548

1

Comments

Inshal Irshad · Accepted Answer · 2021-12-22 14:24:09Z

This might not be a solution but in my case I was having the same issue when I tried to restore schemas one by one in pgAdmin 4. But when I checked all data was present. It seems like it is a bug which is shown in the end of execution.

You may use the query below to check the size of the source database and your current destination where you are restoring it.

SELECT schema_name, 
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY schema_name

Rajesh Varma · Accepted Answer · 2023-12-06 19:45:30Z

-1

I had a similar error with exit code 1:-

Got it resolved by doing restore option at Schema level instead of DB Level to restore data

answered Dec 6, 2023 at 19:45

Rajesh Varma

1492 silver badges10 bronze badges

Collectives™ on Stack Overflow

EXIT CODE 1 when restore sql file in postgres

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related