De Wiki inetshell
Saltar a: navegación, buscar
(Variables)
(Respaldo y restauracion)
 
Línea 61: Línea 61:
 
   echo "Finished backup for $DB" >> /tmp/db_backup_info.log
 
   echo "Finished backup for $DB" >> /tmp/db_backup_info.log
 
done
 
done
 +
</pre>
 +
 +
=== Restaurar cuando existe error ===
 +
https://stackoverflow.com/questions/13410631/how-to-solve-privileges-issues-when-restore-postgresql-database
 +
 +
https://cloud.google.com/sql/docs/postgres/import-export/exporting
 +
 +
* Error:
 +
<pre>
 +
Import failed: SET SET SET SET set_config ------------ (1 row) SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql Import error: exit status 3
 +
</pre>
 +
 +
* Comando para realizar respaldo:
 +
<pre>
 +
pg_dump -U postgres --format=plain --no-owner --no-acl database \
 +
    | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > database.sql
 
</pre>
 
</pre>
  

Revisión actual del 01:34 8 oct 2019

Variables

https://www.postgresql.org/docs/8.3/libpq-envars.html

PGPASSFILE para guardar credenciales en archivo

https://www.postgresql.org/docs/8.3/libpq-pgpass.html

The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise). On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

This file should contain lines of the following format:

hostname:port:database:username:password

cat .pgpass
server:5432:*:username:password

Respaldo y restauracion

Una base de datos

https://www.postgresql.org/docs/9.2/app-pgrestore.html

  • Respaldar
pg_dump -Fc mydb > db.dump
  • Restaurar
dropdb mydb
pg_restore -C -d postgres db.dump

Todas las bases de datos

https://www.postgresql.org/docs/9.1/app-pg-dumpall.html

  • Respaldar
pg_dumpall > db.out
  • Restaurar:
psql -f db.out postgres

Script para respaldo de todas las bases

https://spapas.github.io/2016/11/02/postgresql-backup/

export PGPASSWORD=password123
export TIMESTAMP=$(date +"%Y%m%d_%H%M%S")

export ENV=develop
export PRODUCT=app
export DB_HOST=db.${ENV}.${PRODUCT}

export DATABASES=$(psql -h ${DB_HOST} -U postgres -lt | grep -v : | cut -d \| -f 1 | grep -v template | grep -v -e '^\s*$' | sed -e 's/  *$//'|  tr '\n' ' ')

echo "Will backup: $DATABASES to $backup_dir" >> /tmp/db_backup_info.log

for DB in $DATABASES; do
  echo "Starting backup for ${DB}" >> /tmp/db_backup_info.log
  filename=${TIMESTAMP}-${PRODUCT}-${ENV}-${DB}.sql.gz
  vacuumdb --analyze -h ${DB_HOST} -U postgres $DB >> /tmp/db_backup.log
  pg_dump -h ${DB_HOST} -Upostgres -v $DB -F p 2>> /tmp/db_backup.log | gzip > $filename
  echo "Finished backup for $DB" >> /tmp/db_backup_info.log
done

Restaurar cuando existe error

https://stackoverflow.com/questions/13410631/how-to-solve-privileges-issues-when-restore-postgresql-database

https://cloud.google.com/sql/docs/postgres/import-export/exporting

  • Error:
Import failed: SET SET SET SET set_config ------------ (1 row) SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql Import error: exit status 3 
  • Comando para realizar respaldo:
pg_dump -U postgres --format=plain --no-owner --no-acl database \
    | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' > database.sql

Comandos

Listar sesiones actuales

SELECT usesysid, client_addr, usename FROM pg_stat_activity;