4 ms·

Getting the data into PostgreSQL is a problem in nearly any setup. You can't really copy PostgreSQL's actual data directory around. Subtle differences in config

by rspeer 9y ago

Getting the data into PostgreSQL is a problem in nearly any setup. You can't really copy PostgreSQL's actual data directory around. Subtle differences in configuration (I don't know what they are) will probably cause PostgreSQL to consider the database corrupted and lock up. So you have to export a portable version of the database and spend time loading it back in. The exception is if I make a complete image of the machine, including PostgreSQL and its data, using AWS or VirtualBox or something. If there's a way to package up a self-contained, non-root copy of PostgreSQL with data already in it (like you can do with SQLite), I would love to know about it. And yet I won't give up on PostgreSQL; despite the awkwardness of setting it up, it really is the best database out of many that I've tried.

grzm 9y ago
Yeah, I know what you mean. Best suggestion I have is to get really familiar with pg_dump and pg_restore. It's really quite a powerful pair of tools with a lot of options. The COPY command is also really flexible if you'd like to use CSV for interop with non-PG tools. Likely this is all stuff you already know. At the end of the day, I consider it just part of massaging data between systems. FWIW, you can zip up and move a data directory around with some caveats: the data directory stores the data in ways specific to how the PostgreSQL binary was built (including Postgres version), so if the environment and build is the same, you're often fine. However, that's not a great method if you're looking for portability.
- rspeer 9y ago
  I am using the COPY command; it's faster than pg_restore, as far as I can tell. The COPY plus building the index is the part that takes a few hours and doesn't fit well into Docker.
  - grzm 9y ago
    It depends on how you're using pg_dump: you can choose to dump data and indexes separately. I suspect your COPY + index builds are comparable to the pg_restore, depending on your degree of parallelism with the index builds.

by rspeer 9y ago

grzm 9y ago

Yeah, I know what you mean. Best suggestion I have is to get really familiar with pg_dump and pg_restore. It's really quite a powerful pair of tools with a lot of options. The COPY command is also really flexible if you'd like to use CSV for interop with non-PG tools. Likely this is all stuff you already know. At the end of the day, I consider it just part of massaging data between systems. FWIW, you can zip up and move a data directory around with some caveats: the data directory stores the data in ways specific to how the PostgreSQL binary was built (including Postgres version), so if the environment and build is the same, you're often fine. However, that's not a great method if you're looking for portability.

rspeer 9y ago
I am using the COPY command; it's faster than pg_restore, as far as I can tell. The COPY plus building the index is the part that takes a few hours and doesn't fit well into Docker.
- grzm 9y ago
  It depends on how you're using pg_dump: you can choose to dump data and indexes separately. I suspect your COPY + index builds are comparable to the pg_restore, depending on your degree of parallelism with the index builds.