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

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.

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.

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.