'EXIT CODE 1 when restore sql file in postgres

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 The way that I restore the file my table_dump.sql This is the detail of error The detail of error

python-3.xpostgresqlrestorepgadmin-4pg-restore


Solution 1:[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.

Solution 2:[2]

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.

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 jjanes
Solution 2 user17465548
Solution 3 Inshal irshad

Related Questions