[Solved] import to PostgreSQL

[walter21]

Hi, I'm trying to import a database into phppgadmin, but the message "no server found" appears in the installation document, it is done by linux commands, ex: psql -U [user] -d [db_name] -f /home/walter21/e-city-2018-1-schema.sql, then resolve to put this command, in cron, but also not made the import, please could help me to import, thanks! sorry for my bad English, I am brazilian.

 

[wolstech]

You should be able to import it through the PhpPgAdmin web interface. I doubt the cron would import it. If it won't import though, tell me the database you want that file imported into and I can import it from the command line for you.

[walter21]

Thanks for your reply Wolstech, I tried to import by PhpPgAdmin web interface, but it appeared the message "on the server found", possibly did something wrong, and if you can import it to me, are two SQL files in the link that I am sending: https://www.dropbox.com/sh/qv7yjrjxh7n6ydq/AAAkBhBhWtZZl9MueXsLVCika?dl=0

 

the two files need to be imported into the walter21_city bank, my user is walter21, Thank you very much!

 

[wolstech]

Please upload the files into your home folder and let me know their filenames and I'll go ahead and import them.

[walter21]

Ok, upload the two files to the home folder, their names are "e-city-2018-1-demo.sql" and "e-city-2018-1-schema.sql" for the walter21_city bank, thanks again wolstech.

[wolstech]

Did you create the database walter21_city? It doesn't exist, which would explain a lot about why you can't import into it. The database itself needs to be created through cPanel in the same manner that MySQL databases do.

 

Please create the database. Or is this supposed to be in walter21_cidade?

Edited February 2, 2018 by wolstech

[walter21]

It's walter 21_cidade, sorry for the mistake

[wolstech]

I'm not surprised it wouldn't import for you simply due to its size. Those database files are huge. I copied and fixed the SET OWNER commands on the schema file so it assigns the tables and schemas to a user that exists (you), then started it and its still running. I'm not even going to attempt the demo data right now...

 

Also, it seems happy right now, but there were tons of errors in the beginning, so if it doesn't work we'll need to drop it and try again. 

 

I'll let you know when it's done. 

 

EDIT: I think you've won the award for most tables in a single database on our server  And it's still not done...

Edited February 2, 2018 by wolstech

[wolstech]

Looks like it's got a bunch of syntax issues

psql:cidade_schema.sql:201697: ERROR:  syntax error at or near "LARGE"
LINE 1: ALTER LARGE OBJECT 2606551314 OWNER TO walter21_cidade;
              ^
 lo_create
------------
 2608066841
(1 row)

psql:cidade_schema.sql:201706: ERROR:  syntax error at or near "LARGE"
LINE 1: ALTER LARGE OBJECT 2608066841 OWNER TO walter21_cidade;
              ^
 lo_create
------------
 2608583171
(1 row)

psql:cidade_schema.sql:201715: ERROR:  syntax error at or near "LARGE"
LINE 1: ALTER LARGE OBJECT 2608583171 OWNER TO walter21_cidade;
              ^
 lo_create
------------
 2608589391
(1 row)

psql:cidade_schema.sql:201724: ERROR:  syntax error at or near "LARGE"
LINE 1: ALTER LARGE OBJECT 2608589391 OWNER TO walter21_cidade;
              ^
 lo_create
------------
 2775496897
(1 row)

psql:cidade_schema.sql:201733: ERROR:  syntax error at or near "LARGE"
LINE 1: ALTER LARGE OBJECT 2775496897 OWNER TO walter21_cidade;
              ^
 lo_create
------------
 3611089473
(1 row)

psql:cidade_schema.sql:201742: ERROR:  syntax error at or near "LARGE"
LINE 1: ALTER LARGE OBJECT 3611089473 OWNER TO walter21_cidade;
              ^
 lo_create
-----------
 489583744
(1 row)

psql:cidade_schema.sql:201751: ERROR:  syntax error at or near "LARGE"
LINE 1: ALTER LARGE OBJECT 489583744 OWNER TO walter21_cidade;
              ^
SET

What version postgres was this DB for anyway? If it's meant for 9.x, no wonder it's been spewing errors. Johnny has 8.x...ALTER LARGE OBJECT seems to be a 9.x command. If this is the case, this database simply won't run on Johnny properly.

[wolstech]

So, 2 hours on, this database is both too large for our systems and for the wrong version of postgres (it's for 9.x, we have 8.x on Johnny). This will not run here.

 

It's still finished the import on the schema file, but its almost certainly not going to work correctly. I won't bother to attempt the 400MB of data as I'll only run you out of disk space and whatever this DB goes to is likely not going to work anyway due to the version differences...a significant number of commands have failed at this point due to being for the wrong version.

 

If you want to go ahead, let me know, otherwise you can just drop the database.

Edited February 2, 2018 by wolstech
It finished

[walter21]

Oh my God, I'm sorry for all this work, and thank you very much and congratulations for the effort and commitment you had in helping me solve this problem. The bank really is for version 9.x. I believe that only with the squema that you imported the system works, the other file is just the demo.Can you answer me a question? did you import from the command line, why the phppgadmin interface really would not work? thank you again. Sorry for taking time to respond you.

Edited February 4, 2018 by walter21

[wolstech]

I did import via command line. No idea why the UI wouldn't see the server, but phppgadmin has a limit of like 50MB anyway so even if it did, this particular file won't import through it due to size.

[walter21]

Just one more question, about exporting the database, structure and data, the dump is coming empty, why? I'm following this step-by-step: I click on the database, then click on export, I finally choose structure and data, I choose the SQL format and click on export, it downloads the dump.sql file but it is empty.

[wolstech]

Are you exporting it from our server? If so, more than likely its once again size causing the export to time out and send an empty file. If only the data is missing, it's because there's no data in there (I didn't import the demo data).

 

PHPPgAdmin and postgres really seem to struggle on Johnny due to poor performance too. If you were on Tommy, this database would likely work just fine though disk space might be an issue (all our servers offer the same 1GB, though that NAS fundraiser we're holding hopes to eventually increase that). We have postgres 9.x on Tommy, and the server is also significantly faster.

[walter21]

I exported a specific table and it worked, so it really should be because of the size of the database, I believe all my doubts have been answered, thank you again.