5

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.

2
  • 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. May 6, 2009 at 6:33
  • Thanks Dana. I don't have ssh on the clients dev box. They only have the phpphadmin to use.
    – Gareth
    May 11, 2009 at 5:50

4 Answers 4

4
+100

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.
6
  • 2
    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. May 6, 2009 at 4:36
  • 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.
    – Gareth
    May 6, 2009 at 4:36
  • Make sure the created tables are owned by your user. Also, you can check postgres's logs for a more descriptive error message. May 6, 2009 at 4:38
  • @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."
    – Gareth
    May 6, 2009 at 4:42
  • Yes, and likewise, the user you are using may not have permission to create tables in a database owned by another user. May 6, 2009 at 6:32
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

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.

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .