Home / server / Questions / 5141
Accepted
Gareth
Asked: 2009-05-06 20:05:27 +0800 CST

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

  • 772

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:
alt text

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.

linux database database-administration postgresql

4 Answers

  1. Best Answer

    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.
    • 4

  2. 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.

    • 2

  3. 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.

    • 2

  4. Run, don't walk away from your hosting provider. You want direct access to the data not only thru phppgadmin.

    phppgadmin (as well as phpmyadmin) in those cases have a huge problem. You won't be able to get dumps.

    Reasoning: With PHP and apache you have a max. execution time as well as a memory limit for the scripts running. It may not be a problem now in terms of execution time / memory now but as your database grows you will run into it.

    To get a good dump you will at least need access to the database (which should if the DB is directly exposed to the net of course be SSL secured). Be it thru SSH or directly thru psql, otherwise I don't really see a good option to get dumps. phppgadmin (or phpmyadmin) may be convenient to browse thru the DB make some changes, do some (smaller) selects and so on, but not if you need to get possibly vast amounts of data.

    I've had both tools fail on me on various occasions with PHP timeouts or too much memory usage when doing backups/restores.

    As for a Guide how to Backup the original postgres documentation is usually very good: http://www.postgresql.org/docs/8.3/static/backup.html

    Now for your problem how to get a nice DB dump:

    • Use the "normal" SQL format (yes the docs say it's much slower but I found it to be the most reliable option)
    • Do not "show" the dump in phppgadmin but rather download it (gzipped or not may or may not work depending on the php installation and on the time it takes to create the dump, actually the same is true for the "show" option)

    I can't stress enough to get SSH or psql access directly to the host. If your hosting provider won't give you access, set up a local PostgreSQL create a playground database and send them the pg_dump command you wish to use, let them place it in a SSL and password protected directory and download it from there.

    • 0