postgresql - nube - psql command

Creando una copia de una base de datos en PostgreSQL

Question

¿Cuál es la forma correcta de copiar la base de datos completa (su estructura y datos) a una nueva en pgAdmin?

Answer 1

¿Cuál es la forma correcta de copiar la base de datos completa (su estructura y datos) a una nueva en pgAdmin?

Responder:

CREATE DATABASE newdb WITH TEMPLATE originaldb;

Tratado y probado.

Answer 2

Desde la documentation , no se recomienda el uso de createdb o CREATE DATABASE con plantillas:

Aunque es posible copiar una base de datos que no sea template1 especificando su nombre como la plantilla, esto no está (aún) destinado a ser una función de "COPY DATABASE" de propósito general. La principal limitación es que no se pueden conectar otras sesiones a la base de datos de la plantilla mientras se está copiando. CREATE DATABASE fallará si existe alguna otra conexión cuando se inicie; de lo contrario, las nuevas conexiones a la base de datos de la plantilla se bloquearán hasta que finalice CREATE DATABASE.

postgresql.org/docs/current/static/app-pgdump.html o pg_dumpall es una buena forma de copiar la base de datos Y TODOS LOS DATOS. Si está utilizando una GUI como pgAdmin, estos comandos se llaman entre bastidores cuando ejecuta un comando de copia de seguridad. La copia a una nueva base de datos se realiza en dos fases: Copia de seguridad y restauración

pg_dumpall guarda todas las bases de datos en el cluster PostgreSQL. La desventaja de este enfoque es que usted termina con un archivo de texto potencialmente muy grande lleno de SQL requerido para crear la base de datos y llenar los datos. La ventaja de este enfoque es que obtiene todos los roles (permisos) para el clúster de forma gratuita. Para volcar todas las bases de datos haga esto desde la cuenta del superusuario.

pg_dumpall > db.out

y para restaurar

psql -f db.out postgres

pg_dump tiene algunas opciones de compresión que te dan archivos mucho más pequeños. Tengo una base de datos de producción que hago copias de seguridad dos veces al día con un trabajo cron usando

pg_dump --create --format=custom --compress=5 ==file=db.dump mydatabase

donde compress es el nivel de compresión (0 a 9) y create le dice a pg_dump que agregue comandos para crear la base de datos. Restaure (o mueva a nuevo clúster) usando

pg_restore -d newdb db.dump

donde newdb es el nombre de la base de datos que desea utilizar.

Otras cosas para pensar

PostgreSQL usa ROLES para administrar permisos. Estos no son copiados por pg_dump . Además, no hemos tratado con la configuración en postgresql.conf y pg_hba.conf (si está moviendo la base de datos a otro servidor). Tendrás que descubrir los ajustes de configuración por tu cuenta. Pero hay un truco que acabo de descubrir para respaldar roles. Los roles se administran a nivel de clúster y puede solicitar a pg_dumpall que pg_dumpall solo los roles con el interruptor de línea de comandos --roles-only .

Answer 3

En el entorno de producción, donde la base de datos original está bajo tráfico, simplemente estoy usando:

pg_dump production-db | psql test-db

Answer 4

En pgAdmin puede hacer una copia de seguridad de su base de datos original, y luego simplemente crear una nueva base de datos y restaurar desde la copia de seguridad que acaba de crear:

1. Haga clic derecho en la base de datos de origen, Copia de seguridad ... y volcar en un archivo.
2. Haga clic con el botón derecho, Nuevo objeto, Nueva base de datos ... y nombre el destino.
3. Haga clic derecho en la nueva base de datos, Restaurar ... y seleccione su archivo.

Answer 5

No sé acerca de pgAdmin, pero pgdump le da un volcado de la base de datos en SQL. Solo necesita crear una base de datos con el mismo nombre y hacer

psql mydatabase < my dump

para restaurar todas las tablas y sus datos y todos los privilegios de acceso.

Answer 6

Para aquellos que todavía están interesados, he creado un script de bash que hace (más o menos) lo que el autor quería. Tuve que hacer una copia diaria de la base de datos de negocios en un sistema de producción, esta secuencia de comandos parece hacer el truco. Recuerde cambiar el nombre de la base de datos / usuario / pw valores.

#!/bin/bash

if [ 1 -ne $# ]
then
  echo "Usage `basename $0` {tar.gz database file}"
  exit 65;
fi

if [ -f "$1" ]
then
  EXTRACTED=`tar -xzvf $1`
  echo "using database archive: $EXTRACTED";
else
  echo "file $1 does not exist"
  exit 1
fi


PGUSER=dbuser
PGPASSWORD=dbpw
export PGUSER PGPASSWORD

datestr=`date +%Y%m%d`


dbname="dbcpy_$datestr"
createdbcmd="CREATE DATABASE $dbname WITH OWNER = postgres ENCODING = ''UTF8'' TABLESPACE = pg_default LC_COLLATE = ''en_US.UTF-8'' LC_CTYPE = ''en_US.UTF-8'' CONNECTION LIMIT = -1;"
dropdbcmp="DROP DATABASE $dbname"

echo "creating database $dbname"
psql -c "$createdbcmd"

rc=$?
if [[ $rc != 0 ]] ; then
  rm -rf "$EXTRACTED"
  echo "error occured while creating database $dbname ($rc)"
  exit $rc
fi


echo "loading data into database"
psql $dbname < $EXTRACTED > /dev/null

rc=$?

rm -rf "$EXTRACTED"

if [[ $rc != 0 ]] ; then
  psql -c "$dropdbcmd"
  echo "error occured while loading data to database $dbname ($rc)"
  exit $rc
fi


echo "finished OK"

Answer 7

Para clonar una base de datos existente con postgres puedes hacer eso

/* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = ''SOURCE_DB'' AND pid <> pg_backend_pid();

/* CLONE DATABASE TO NEW ONE(TARGET_DB) */
CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;

IT eliminará todas las conexiones a la fuente db evitando el error

ERROR:  source database "SOURCE_DB" is being accessed by other users

Answer 8

Para crear volcado de base de datos

cd /var/lib/pgsql/
pg_dump database_name> database_name.out

Para volver a la base de datos volcado

psql -d template1
CREATE DATABASE database_name WITH  ENCODING ''UTF8'' LC_CTYPE ''en_US.UTF-8'' LC_COLLATE ''en_US.UTF-   8'' TEMPLATE template0;
CREATE USER  role_name WITH PASSWORD ''password'';
ALTER DATABASE database_name OWNER TO role_name;
ALTER USER role_name CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE database_name to role_name;


CTR+D(logout from pgsql console)
cd /var/lib/pgsql/

psql -d database_name -f database_name.out

Answer 9

PostgreSQL 9.1.2:

$ CREATEDB new_db_name -T orig_db_name -O db_user;

Answer 10

Postgres permite el uso de cualquier base de datos existente en el servidor como plantilla al crear una nueva base de datos. No estoy seguro de si pgAdmin le da la opción en el cuadro de diálogo crear base de datos, pero no podrá ejecutar lo siguiente en una ventana de consulta si no lo hace:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Aún así, puede obtener:

ERROR:  source database "originaldb" is being accessed by other users

Para solucionarlo puedes utilizar esta consulta.

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = ''originaldb'' AND pid <> pg_backend_pid();

Answer 11

Primero, sudo como usuario de la base de datos:

sudo su postgres

Ir a la línea de comandos de PostgreSQL:

psql

Cree la nueva base de datos, otorgue los derechos y salga:

CREATE DATABASE new_database_name;
GRANT ALL PRIVILEGES ON DATABASE new_database_name TO my_user;
/d

Copie la estructura y los datos de la antigua base de datos a la nueva:

pg_dump old_database_name | psql new_database_name

Answer 12

Prueba esto:

CREATE DATABASE newdb WITH ENCODING=''UTF8'' OWNER=owner TEMPLATE=templatedb LC_COLLATE=''en_US.UTF-8'' LC_CTYPE=''en_US.UTF-8'' CONNECTION LIMIT=-1;

gl XD

Answer 13

Puse este enfoque junto con los ejemplos de arriba. Estoy trabajando en un servidor "bajo carga" y recibí el error cuando intenté el enfoque desde @zbyszek. También buscaba una solución de "solo línea de comandos".

createdb: database creation failed: ERROR: source database "exampledb" is being accessed by other users .

Esto es lo que funcionó para mí (los comandos precedieron con nohup para mover la salida a un archivo y protegerlo de la desconexión del servidor ):

1. nohup pg_dump exampledb > example-01.sql
2. createdb -O postgres exampledbclone_01

   mi usuario es "postgres"

3. nohup psql exampledbclone_01 < example-01.sql

Answer 14

Si la base de datos tiene conexiones abiertas, esta secuencia de comandos puede ayudar. Lo uso para crear una base de datos de prueba a partir de una copia de seguridad de la base de datos de producción en vivo todas las noches. Esto supone que tiene un archivo de copia de seguridad .SQL de la base de datos de producción (hago esto dentro de webmin).

#!/bin/sh

dbname="desired_db_name_of_test_enviroment"
username="user_name"
fname="/path to /ExistingBackupFileOfLive.sql"

dropdbcmp="DROP DATABASE $dbname"
createdbcmd="CREATE DATABASE $dbname WITH OWNER = $username "

export PGPASSWORD=MyPassword



echo "**********"
echo "** Dropping $dbname"
psql -d postgres -h localhost -U "$username" -c "$dropdbcmp"

echo "**********"
echo "** Creating database $dbname"
psql -d postgres -h localhost -U "$username" -c "$createdbcmd"

echo "**********"
echo "** Loading data into database"
psql -d postgres -h localhost -U "$username" -d "$dbname" -a -f "$fname"

Answer 15

Una versión de línea de comandos de la respuesta de Bell :

createdb -O ownername -T originaldb newdb

Esto debe ejecutarse bajo los privilegios del maestro de la base de datos, generalmente postgres.

Answer 16

Usando pgAdmin, desconecte la base de datos que desea usar como plantilla. Luego lo selecciona como la plantilla para crear la nueva base de datos, esto evita que se produzca el error que ya está en uso.