Postgres pg_dump How to make a backup with little space

3

I have a Server Centos 6.9 con Postgres 9.2 and a base that weighs 70 GB I have little space available and I can not add more space . I need to do a backup of the base. The problem is when running the pg_dump process is cut due to lack of memory. The question is: is there any possibility of extracting the base and restoring it elsewhere? I do the backup remote, but I still get the error. Sorry for these questions since I am new with Postgres . Execute:

pg_dump -i -h localhost -p 5432 -U user -F c -b -v -f "/ruta/respaldo.backup" mybase

thank you very much!

    
asked by Ivox 05.12.2018 в 22:13
source

1 answer

0
  • You can try to tunnel to that machine to that port and restore it or make a backup from any other. From your preferred machine create a tunnel to that other machine where you have the database.

    You make this tunnel that passes traffic from port 5432 from the remote machine to the local one, at local port 5432.

    $ ssh -N -f -L 5432:127.0.0.1:5432 <usuario_de_maquina_remota>@<ip_remota>  
    

    Once connecting the ports of both machines, you can run on any machine where you did this

    $ pg_dump -i -h localhost -p 5432 -U user -b -v -f "/ruta/respaldo.backup" dbname 
    

    And the backup will be saved in any other where you run these commands.
    Here the remote machine is the one with your database and the local one is where you want to restore or make a backup.

  • You can send that backup to another machine and restore it there by running this command on your machine where you have the database and having access by ssh to the machine (clearly with enough memory).

    $ pg_dump -C -v -h localhost -p 5432 -U user dbname | bzip | ssh  <usuario_remoto>@>host_remoto> "bunzip2 | psql dbname"
    

    Here the remote machine is where you are going to send the compressed database and then you will restore it in the one-liner and the local one is where you have the database .

  • Of course you have to have access by ssh on both machines from both machines.

        
    answered by 05.12.2018 в 22:45