exportar - funciones y procedimientos en postgresql

Copiando por completo una tabla de postgres con SQL

Question

DESCARGO DE RESPONSABILIDAD: Esta pregunta es similar a la pregunta de desbordamiento de pila aquí , pero ninguna de esas respuestas funciona para mi problema, como explicaré más adelante.

Estoy intentando copiar una tabla grande (~ 40M filas, más de 100 columnas) en postgres donde muchas de las columnas están indexadas. Actualmente uso este bit de SQL:

CREATE TABLE <tablename>_copy (LIKE <tablename> INCLUDING ALL);
INSERT INTO <tablename>_copy SELECT * FROM <tablename>;

Este método tiene dos problemas:

1. Agrega los índices antes de la ingesta de datos, por lo que tomará mucho más tiempo que crear la tabla sin índices y luego indexarlos después de copiar todos los datos.
2. Esto no copia las columnas de estilo `SERIAL ''correctamente. En lugar de configurar un nuevo ''contador'' en la nueva tabla, establece el valor predeterminado de la columna en la nueva tabla en el contador de la tabla anterior, lo que significa que no aumentará a medida que se agreguen las filas.

El tamaño de la tabla hace que la indexación sea un problema en tiempo real. También hace que no sea factible volcar en un archivo para luego reinsertarlo. Tampoco tengo la ventaja de una línea de comando. Necesito hacer esto en SQL.

Lo que me gustaría hacer es o bien hacer una copia exacta con algún comando milagro, o si eso no es posible, copiar la tabla con todas las contraints pero sin índices, y asegurarme de que son las restricciones ''en espíritu'' (aka un nuevo contador para una columna SERIAL). Luego copie todos los datos con un SELECT * y luego copie todos los índices.

Fuentes

1. Pregunta de Stack Overflow sobre la copia de bases de datos : Esto no es lo que estoy pidiendo por tres razones

   • Utiliza la opción de línea de comando pg_dump -t x2 | sed ''s/x2/x3/g'' | psql pg_dump -t x2 | sed ''s/x2/x3/g'' | psql pg_dump -t x2 | sed ''s/x2/x3/g'' | psql y en este contexto no tengo acceso a la línea de comando
   • Crea los índices de ingesta de datos previos, que es lenta
   • No actualiza las columnas seriales correctamente como evidencia por default nextval(''x1_id_seq''::regclass)

2. Método para restablecer el valor de secuencia para una tabla de postgres : Esto es genial, pero desafortunadamente es muy manual.

Answer 1

El "comando milagro" más cercano es algo así como

pg_dump -t tablename | sed -r ''s//btablename/b/tablename_copy/'' | psql -f -

En particular, esto se encarga de crear los índices después de cargar los datos de la tabla.

Pero eso no restablece las secuencias; tendrás que escribir eso tú mismo.

Answer 2

Aparentemente quieres "reconstruir" una mesa. Si solo quiere reconstruir una tabla, no la copie, entonces debería usar CLUSTER en su lugar.

SELECT count(*) FROM table; -- make a seq scan to make sure the table is at least
                            -- decently cached
CLUSTER someindex ON table;

Puedes elegir el índice, intenta elegir uno que se adapte a tus consultas. Siempre puede usar la clave principal si no hay otro índice adecuado.

Si su tabla es demasiado grande para ser almacenada en la memoria caché, CLUSTER puede ser lento.

Answer 3

Bien, vas a tener que hacer algunas de estas cosas a mano, desafortunadamente. Pero todo se puede hacer desde algo como psql. El primer comando es bastante simple:

select * into newtable from oldtable

Esto creará newtable con los datos de la tabla de antigüedad, pero no los índices. Luego tienes que crear los índices y secuencias, etc. por tu cuenta. Puede obtener una lista de todos los índices en una tabla con el comando:

select indexdef from pg_indexes where tablename=''oldtable'';

Luego ejecute psql -E para acceder a su base de datos y use / d para mirar la tabla anterior. A continuación, puede manipular estas dos consultas para obtener la información sobre las secuencias:

SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ ''^(oldtable)$''
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = ''74359'' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

Reemplace ese 74359 anterior con el oid que obtiene de la consulta anterior.

Answer 4

ADVERTENCIA:

Todas las respuestas que usan pg_dump y cualquier tipo de expresión regular para reemplazar el nombre de la tabla fuente son realmente peligrosas. ¿Qué pasa si sus datos contienen la subcadena que está tratando de reemplazar? ¡Terminarás cambiando tus datos!

Propongo una solución de dos pasos:

1. eliminar las líneas de datos del volcado utilizando algunos regexp específicos de datos
2. realizar búsqueda y reemplazo en las líneas restantes

Aquí hay un ejemplo escrito en Ruby:

ruby -pe ''gsub(/(members?)/, "//1_copy_20130320") unless $_ =~ /^/d+/t.*(?:t|f)$/'' < members-production-20130320.sql > copy_members_table-20130320.sql

En lo anterior, intento copiar la tabla de "miembros" en "members_copy_20130320". Mi expresión regular específica de datos es /^/d+/t.*(?:t|f)$/

El tipo de solución anterior funciona para mí. Caveat Emptor ...

editar:

OK, aquí hay otra forma en la sintaxis de pseudo-shell para las personas regexp-aversión:

1. pg_dump -s -t mytable mydb> mytable_schema.sql
2. buscar y reemplazar el nombre de la tabla en mytable_schema.sql> mytable_copy_schema.sql

3. psql -f mytable_copy_schema.sql mydb

4. pg_dump -a -t mytable mydb> mytable_data.sql

5. reemplace "mytable" en las pocas declaraciones SQL que preceden a la sección de datos
6. psql -f mytable_data.sql mydb

Answer 5

La create table as característica en PostgreSQL puede ser ahora la respuesta que el OP estaba buscando.

https://www.postgresql.org/docs/9.5/static/sql-createtableas.html

create table my_table_copy as
  select * from my_table

Esto creará una tabla idéntica con los datos.

Agregar with no data copiará el esquema sin los datos.

create table my_table_copy as
  select * from my_table
with no data

Esto creará la tabla con todos los datos, pero sin índices ni activadores, etc.

create table my_table_copy (like my_table including all)

La sintaxis de creación de tablas incluirá todos los factores desencadenantes, índices, restricciones, etc., pero no incluirá datos.

Answer 6

create table newTableName (como oldTableName incluyendo índices); insertar en newTableName select * from oldTableName

Esto funcionó para mí 9.3