Can you explain exporting and then importing PostgreSQL using phpPgAdmin? (And what's with permissions?)

Question

Bounty Edit: I've left the original question but would like a good answer on the whole permissions debacle that Postgres seems to bath in.
'Importing & exporting clients db's: A field guide'

I just got this job today where we need to use PostgreSQL.

The client has two hosts and they only use phpPgAdmin to access the database. There's no SSH.

We need to the export the data and then import it to a local dev machine.

Being wise in the way of MySQL I'm a little lost as to the correct process.

There's PostgreSQL >> database >> export
and from there one gets:

I've been hitting up the documentation but to tell you the truth, I can't find a simple explanation and don't really care about the db or Postgres at the moment. All we need to do is tackle the sites PHP.

Hit me up with a synopsis/cheatsheet/guide my sysadminy syblings.

Edits:
Changed the question to be include the PostgreSQL permissions on importing a db.
There really does seem to be more to setting up a new PostgreSQL server as far as permissions go.

I ask specifically about phpPgAdmin because my server is not localhost & has no GUI.

I get a feeling that it's a permissions thing http://pgedit.com/public/sql/acl_admin/index.html

Clarified no SSH on clients box.

Still can't find concise documentation on this whole process. Am really avoiding task till I've a clearer idea. Might look to convert into MySQL db for ease.

Solution 1

Export

1. Login
2. Navigate to the "Export" tab
3. Click the "Structure and Data" radio button
4. Click the "Download" radio button
5. Click the "Export" Button and save it to your local machine.

Import

1. Navigate to http://www.pgadmin.org/ to download pgAdmin III (one of the better free Postgres GUI tools)
2. Install it
3. Use it for all your Postgres needs.

Solution 2

phpPgAdmin export uses pg_dump. You can read up on that if you want to know what it's doing.

You may also want to export it as SQL instead of COPY. COPY is for Postgres and you'll need SQL if you want to try to use a different database. It's also easier to understand if your used to different databases.

What you'll get with 'Structure and Data' is an sql file that will start with the database table structure, then all the data, then constraints and keys.

As sascha has pointed out, you may be having issues with the permissions. I've always found it easier to just delete the permission lines or comment them out since this is just for a development database. Remove/comment/change the lines with 'OWNER' in them. Like

ALTER TABLE schema.table OWNER TO pguser;

Each CREATE TABLE statement will have one of these after it to set the table owner.

IMPORTING

If you have SQL errors (which I've seen quite often, depending on the data. Strings with odd characters can be a pain) it might be easier to split up the file and just copy out the structure first and run that as a standard SQL query. Then start with the data and import it one table at a time. It makes it much easier to find the error when you cut it into parts. Especially when dealing with the very unhelpful error messages.

Yes, it's a lot of copy and paste work but it gets the job done and will probably take less time then porting it into MySQL and then having to change the PHP to connect and get data from MySQL instead of Postgres.

PhpPgAdmin and pgadminIII both have the ability to run SQL statements.

pgAdminIII -> select database -> select 'Run arbitrary SQL Queries' button. PhpPgAdmin -> select database -> select the 'SQL' link.

Honestly this is the least irritating way of moving databases that I've found. It takes longer, but only if you don't run into errors.

Solution 3

In regards to permissions, just open up the database dump (it's plain text) and look for the lines where it tries to change the current user or sets the owner of a table. Then just do a search/replace and replace the original owner with your new username. No need to worry about granting/changing permissions at all :D

We used to have to do this all the time when moving from dev through to production, the original db/table owner would be the developer and we needed to change that to the customers account once we were ready to launch.

We always worked from the console using pg_dump and other related command line tools, but I expect these map to the options you've got in the web interface.

Comments

[Dana the Sane]

Regarding your edit, both pgadmin and pg_dump can be run from a remote machine if postgres is listening for external connections. If this isn't the case, you can use pg_dump on the commandline on the server itself.

[Gareth]

Thanks Dana. I don't have ssh on the clients dev box. They only have the phpphadmin to use.

[Dana the Sane]

pgadmin3 is a much better manager than the php alternative. Being able to easily stop slow queries is better than watching the web version lock up.

[Gareth]

I've installed postgres & phpdbmyadmin and created my own user & password. I can create a db but if I attempt to import the file I get a very unhelpful & generic non-error "SQL error: In statement:". That's why I was hoping for some guide on how to set permissions as they seem to be the problem and quite different to how mysql does it.

[Dana the Sane]

Make sure the created tables are owned by your user. Also, you can check postgres's logs for a more descriptive error message.

[Gareth]

@Dana the Sane: Thanks. So table permissions are transfered. Therefore this is possibly true? "If the objects in the original database were owned by different users, then the dump will instruct psql to connect as each affected user in turn and then create the relevant objects. This way the original ownership is preserved. This also means, however, that all these user must already exist, and furthermore that you must be allowed to connect as each of them. It might therefore be necessary to temporarily relax the client authentication settings."

[Dana the Sane]

Yes, and likewise, the user you are using may not have permission to create tables in a database owned by another user.

[Gareth]

And three months later I'd just like to point out how frigging stupid this is. MySQL is as bad. Merging users/permissions is teh suck. Fail db gods, fail.