Moving Postgresql databases semi automatic script

Doug Easterbrook doug at artsman.com
Fri Feb 23 16:47:01 UTC 2024


hi Mats.

you don’t mention why the move of the database or what the purpose is.  i.e. is it.

- for upgrading your postgres version
- for purposes of making a standalone backup server
- for purposes of making a hot replicaiton of the main database server
- to physically move that database server from one machine to another
- to split a database out of one server containing many databases so that it is moving to its own server
- move server load from one machine to another

there are numerous options that can be done, depending on the case

if its just really upgrading postgres, you can use pg_migrate, remove the old server (don’t delete the data directory) and install the new version of postgres and migrate the DB.    the actual data doesn’t move.  

making a replication server that is always a hot copy of the main one — we use that feature.   it takes time but uses pg_basebackup to get the initial backup


moving one database out of a server - pg_dump and pg_restore is tried and true approach for a single database.


moving all databases from one place to another.      pg_dumpall works to make a dump of all databases


To physically move a server where your data is on one drive… you can stop the server and either move the entire postgres folder, or diskcopy the drive or physically move the drive..     I’ve done either one of those a couple of times in my life.   In one case, we had a machine physically die and simply moved the drives from one machine to another



and if you do pg_dump without any of the compression switches (i.e. don’t use the -F c switches), then you can pg_dump and pipe it into a pg_restore command to a different machine, something to the effect of the following 

pg_dump databasename | pg_restore -h 192.168.x.x  -p 5432  -U username -j NoOfJobs -d databasename





If your goal is to do be able to move servers from one machine to another at will, then you can consider various VM’s.       We use proxmox on top of CEPH clusters and can logically move an entire VM from one place to another, to spark it up in a new location.    Could be done with docker or any other kind of virtualization tools.

This we do since we have multiple machines in our own rack space at two data centres at separate ends of Canada and can orchestrate moving VM's from one data Center to another fairly easily.   or if its just one database, we do the pg_dump | pg_restore. (piping output from pg_dump to input of pg_restore).  





as with everything, the context of your needs or problem at hand might help dictate what you want to do.





Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On Feb 23, 2024, at 1:19 AM, Mats <mats at adjob.se> wrote:
> 
> Hi,
> 
> I have used pg_dump and pg_restore manually to move a database from one place to another.
> 
> Now I had the challenge to move many database to new locations: The first thing i thought of was to create a script in Omnis.
> But as I had no idea how to do this and got no help from anywhere, so I looked at other solutions.
> 
> So instead I created (with some support from AI) a script based on pg_dump and pg_restore from pgAdmin using Go instead.
> Here is a tutorial how I did it. It sure can be translated into Omnis or other languages.
> 
> I thought somebody else could get some ideas if and when the need for moving databases arises
> 
> https://dump.go4webdev.org/
> 
> FWIW,
> 
> /Mats
> 
> 
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com



More information about the omnisdev-en mailing list