Friday 29 September 2023

Postgres table with geographic data - migration commands

 First let's export a table to the file system:

pg_dump --host localhost --port 5432 --username mypgusername --format plain --verbose --file /var/lib/pgsql/stations.sql --table mypgschamename.mypgtablename mydatabasename

Now we have a sql file on filesystem - ready to be migrated.

We can do that even for a remote PG server:

psql -h -p 5432 -d mydbname -U myusername -W -f stations.sql

After putting  in the password for the remote server the restore takes place over the wire.

On WAN network it might be slow and can disconnect occasionally - I'd say several hundred of MB table still can easily be restored like this.

No comments:

Post a Comment