Hogar › Ajustes › Comandos básicos de PostgreSQL. Comandos básicos de PostgreSQL Cómo ver qué versión de PostgreSQL se está ejecutando

Comandos básicos de PostgreSQL. Comandos básicos de PostgreSQL Cómo ver qué versión de PostgreSQL se está ejecutando

En este artículo te mostraré los 15 comandos más útiles para gestionar postgreSQL.

1. ¿Cómo cambiar la contraseña de root en PostgreSQL?

$ /usr/local/pgsql/bin/psql postgres postgres Contraseña: (contraseña antigua) # ALTERAR USUARIO postgres CON CONTRASEÑA 'tmppassword'; $ /usr/local/pgsql/bin/psql postgres postgres Contraseña: (tmpcontraseña)

El cambio de contraseña para un usuario normal se realiza de la misma forma. El usuario root puede cambiar la contraseña de cualquier usuario.

# ALTERAR EL nombre de usuario del USUARIO CON CONTRASEÑA 'tmppassword';

2. ¿Cómo instalar PostgreSQL en inicio automático?

$ su - root # tar xvfz postgresql-8.3.7.tar.gz # cd postgresql-8.3.7 # cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chmod a+x / etc/rc.d/init.d/postgresql

3. Verifique el estado del servidor

$ /etc/init.d/postgresql estado Contraseña: pg_ctl: el servidor se está ejecutando (PID: 6171) /usr/local/pgsql/bin/postgres “-D” “/usr/local/pgsql/data” [ Comentario: Este mensaje indica que el servidor se está ejecutando y funciona normalmente] $ /etc/init.d/postgresql estado Contraseña: pg_ctl: no hay ningún servidor ejecutándose [ Comentario: Este mensaje indica que el servidor no se está ejecutando]

4. ¿Cómo iniciar, detener y reiniciar PostgreSQL?

# parada del servicio postgresql Deteniendo PostgreSQL: el servidor se detuvo correctamente # inicio del servicio postgresql Iniciando PostgreSQL: ok # reinicio del servicio postgresql Reiniciando PostgreSQL: el servidor se detuvo correctamente

5. ¿Cómo puedo ver qué versión de PostgreSQL se está ejecutando?

$ /usr/local/pgsql/bin/psql prueba Bienvenido a psql 8.3.7, la terminal interactiva de PostgreSQL. Escriba: \copyright para términos de distribución \h para obtener ayuda con los comandos SQL \? para obtener ayuda con los comandos psql \g o terminar con punto y coma para ejecutar la consulta \q para salir de la prueba=# seleccionar versión(); versión ————————————————————————————————— PostgreSQL 8.3.7 en i686-pc-linux-gnu, compilado por GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 fila) prueba=#

5. ¿Cómo crear un usuario en PostgreSQL?

Hay dos métodos para esto.

Método 1: Creamos un usuario usando el shell PSQL usando el comando CREATE USER.

# CREAR USUARIO ramesh CON contraseña 'tmppassword'; CREAR PAPEL

Método2: Creamos un usuario usando el comando de shell createuser.

$ /usr/local/pgsql/bin/createuser sathiya¿El nuevo rol será el de superusuario? (sí/no) n ¿Se le permitirá al nuevo rol crear bases de datos? (s/n) n ¿Se le permitirá al nuevo rol crear más roles nuevos? (s/n) n CREAR ROL

6. ¿Cómo crear una base de datos en PostgreSQL?

Hay 2 métodos para esto.

Método1: Creamos una base de datos a través de un shell PSQL usando el comando CREATE DATABASE.

# CREAR BASE DE DATOS mydb CON EL PROPIETARIO ramesh; CREAR BASE DE DATOS

Método2: Usamos el comando creadob.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh CREAR BASE DE DATOS

7. ¿Obtenemos una lista de todas las bases de datos en Postgresql?

8. ¿Cómo eliminar una base de datos en PostgreSQL?

9. Utilice la ayuda integrada para los comandos.

Equipo \? mostrará una línea de ayuda para el comando PSQL. \h CREATE mostrará ayuda para todos los comandos que comiencen con CREATE.

# \? # \h CREAR # \h CREAR ÍNDICE

10. ¿Cómo obtener una lista de todas las tablas de una base de datos determinada en Postgresql?

# \d

Para una base de datos vacía, recibirá el mensaje "No se encontraron relaciones".

11. ¿Cómo puedo saber el tiempo de ejecución de la solicitud?

# \timing: después de ejecutar este comando, cada solicitud posterior mostrará el tiempo de ejecución.

# \momento El tiempo está en marcha. # SELECCIONAR * de pg_catalog.pg_attribute; Tiempo: 9,583 ms

12. ¿Cómo hacer copias de seguridad y restaurar bases de datos y tablas en PostgreSQL?

Esta pregunta es bastante amplia y la publicaré más adelante en un artículo aparte.

13. ¿Cómo ver la lista de funciones disponibles en PostgreSQL?

Para obtener una lista de funciones disponibles, diga \df+

# \df # \df+

14. ¿Cómo editar una consulta PostgreSQL en el editor?

# \mi

\abrirá un editor donde podrá editar la consulta y guardarla.

15. ¿Dónde puedo encontrar el archivo histórico de PostgreSQL?

De manera similar al archivo ~/.bash_history, postgreSQL almacena todos los comandos sql en el archivo ~/.psql_history.

$ gato ~/.psql_history modificar el usuario postgres con la contraseña 'tmppassword'; \h alterar la versión seleccionada por el usuario(); crear usuario ramesh con contraseña 'tmppassword'; \timing select * de pg_catalog.pg_attribute;

Hola a todos, hoy quiero hacer un breve recordatorio sobre los principales comandos de PostgreSQL. Puede trabajar con PosgreSQL tanto de forma interactiva como desde la línea de comandos. El programa es psql. Estoy seguro de que esta lista te resultará muy útil y te ahorrará tiempo buscando entre diferentes recursos. Permítanme recordarles que este es un proyecto de código abierto, basado en el DBMS Postgres, lanzado en 1986, está siendo desarrollado por un grupo mundial de desarrolladores de PGDG, esencialmente de 5 a 8 personas, pero a pesar de esto, se está desarrollando de manera muy intensiva. , introduciendo nuevas funciones y corrigiendo errores y errores antiguos.

Comandos básicos de PostgreSQL en modo interactivo:

\connect db_name – conectarse a la base de datos llamada db_name
\du – lista de usuarios
\dp (o \z) – lista de tablas, vistas, secuencias, derechos de acceso a ellas
\di – índices
\ds – secuencias
\dt – lista de tablas
\dt+ - lista de todas las tablas con descripciones
\dt *s* - lista de todas las tablas que contienen s en el nombre
\dv – representaciones
\dS – tablas del sistema
\d+ – descripción de la tabla
\o – envía los resultados de la consulta a un archivo
\l – lista de bases de datos
\i – leer datos entrantes de un archivo
\e: abre el contenido actual del búfer de solicitud en el editor (a menos que se especifique lo contrario en el entorno de la variable EDITOR, se usará vi de forma predeterminada)
\d “nombre_tabla” – descripción de la tabla
\i ejecutando un comando desde un archivo externo, por ejemplo \i /my/directory/my.sql
\pset – comando para configurar opciones de formato
\echo – muestra un mensaje
\set: establece el valor de una variable de entorno. Sin parámetros, muestra una lista de variables actuales (\unset – elimina).
\? – referencia psql
\ayuda – referencia SQL
\q (o Ctrl+D) – salir del programa

Trabajar con PostgreSQL desde la línea de comando:

-c (o –comando): ejecuta un comando SQL sin entrar en modo interactivo
-f archivo.sql - ejecuta comandos desde el archivo archivo.sql
-l (o –list): muestra una lista de bases de datos disponibles
-U (o –nombre de usuario): especifique el nombre de usuario (por ejemplo, postgres)
-W (o –contraseña) – solicitud de contraseña
-d nombrebd - conectarse a la base de datos nombrebd
-h – nombre de host (servidor)
-s – modo paso a paso, es decir, deberás confirmar todos los comandos
–S – modo de una sola línea, es decir, ir a una nueva línea ejecutará la consulta (se deshace de; al final de la construcción SQL)
-V – versión de PostgreSQL sin entrar en modo interactivo

Ejemplos:

psql -U postgres -d dbname -c “CREAR TABLA mi (algún_id serial CLAVE PRIMARIA, algún_texto texto);” - ejecución de un comando en la base de datos dbname.

psql -d dbname -H -c “SELECT * FROM my” -o my.html: genera el resultado de la consulta en un archivo html.

Utilidades (programas) de PosgreSQL:

createb y dropdb: crea y elimina una base de datos (respectivamente)
createuser y dropuser: creación y usuario (respectivamente)
pg_ctl: un programa diseñado para resolver tareas de control generales (iniciar, detener, configurar parámetros, etc.)
postmaster: módulo de servidor PostgreSQL multiusuario (configurando niveles de depuración, puertos, directorios de datos)
initdb: creación de nuevos clústeres de PostgreSQL
initlocation: un programa para crear directorios para el almacenamiento de bases de datos secundarias
VacuumDB: soporte de bases de datos físicas y analíticas
pg_dump – archivar y restaurar datos
pg_dumpall: realiza una copia de seguridad de todo el clúster de PostgreSQL
pg_restore – restauración de bases de datos desde archivos (.tar, .tar.gz)

Ejemplos de creación de copias de seguridad:

Creando una copia de seguridad de la base de datos mydb, en forma comprimida

Pg_dump -h localhost -p 5440 -U algún usuario -F c -b -v -f mydb.backup mydb

Crear una copia de seguridad de la base de datos mydb, en forma de un archivo de texto normal, incluido el comando para crear la base de datos

Pg_dump -h localhost -p 5432 -U algún usuario -C -F p -b -v -f mydb.backup mydb

Crear una copia de seguridad de la base de datos mydb, en forma comprimida, con tablas que contienen pagos en el nombre

Pg_dump -h localhost -p 5432 -U algún usuario -F c -b -v -t *pagos* -f tablas_de_pagos.backup mydb

Vuelque datos de una sola tabla específica. Si es necesario realizar una copia de seguridad de varias tablas, los nombres de las tablas se enumeran utilizando el modificador -t para cada tabla.

Pg_dump -a -t nombre_tabla -f nombre_archivo nombre_base de datos

Creando una copia de seguridad con compresión gz

Pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Lista de opciones más utilizadas:

-h host - host, si no se especifica, se utiliza localhost o el valor de la variable de entorno PGHOST.
-p puerto: puerto; si no se especifica, se utiliza 5432 o el valor de la variable de entorno PGPORT.
-u - usuario, si no se especifica, se utiliza el usuario actual, el valor también se puede especificar en la variable de entorno PGUSER.
-a, -data-only: volca solo datos de forma predeterminada, se guardan los datos y el esquema.
-b: incluye objetos grandes (blogs) en el volcado.
-s, -schema-only: volca solo el esquema.
-C, -create: agrega un comando para crear una base de datos.
-c: agrega comandos para eliminar (eliminar) objetos (tablas, vistas, etc.).
-O: no agregue comandos para establecer el propietario de un objeto (tablas, vistas, etc.).
-F, -format (c|t|p): formato de salida de volcado, personalizado, tar o texto sin formato.
-t, -table=TABLE: especifica una tabla específica para el volcado.
-v, -verbose: genera información detallada.
-D, -attribute-inserts: volcado usando el comando INSERT con una lista de nombres de propiedades.

Haga una copia de seguridad de todas las bases de datos utilizando el comando pg_dumpall.

Pg_dumpall > todo.sql

Restaurar tablas a partir de copias de seguridad:

psql: restauración de copias de seguridad almacenadas en un archivo de texto sin formato;
pg_restore: restauración de copias de seguridad comprimidas (tar);

Restaurar una copia de seguridad completa ignorando los errores

Psql -h localhost -U algún usuario -d nombrebd -f mydb.sql

Restaurar una copia de seguridad completa, deteniéndose en el primer error

Psql -h localhost -U algún usuario -set ON_ERROR_STOP=on -f mydb.sql

Para restaurar desde un archivo tar, primero necesitamos crear una base de datos usando CREATE DATABASE mydb; (si no se especificó la opción -C al crear la copia de seguridad) y restaurar

Pg_restore -dbname=mydb -jobs=4 -verbose mydb.backup

Restaurar una copia de seguridad de una base de datos comprimida con gz

psql -U postgres -d mibd -f mibd

Creo que la base de datos postgresql ahora le resultará más comprensible. Espero que esta lista de comandos de PostgreSQL te haya resultado útil.

postgres=# CREAR BASE DE DATOS test_database; CREAR BASE DE DATOS postgres=# CREAR USUARIO test_user CON contraseña "qwerty"; CREAR ROL postgres=# OTORGAR TODOS los privilegios EN LA BASE DE DATOS test_database A test_user; CONCEDER

Para salir del shell, ingrese el comando \q.

Ahora intentemos trabajar con la base de datos creada en nombre de test_user:

Psql -h localhost base_datos_prueba usuario_prueba

Creemos una nueva tabla:

Test_database=> CREAR SECUENCIA user_ids; CREAR SECUENCIA test_database=> CREAR TABLA usuarios (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("user_ids"), inicio de sesión CHAR(64), contraseña CHAR(64)); AVISO: CREAR TABLA/CLAVE PRIMARIA CREARÁ UN ÍNDICE implícito "users_pkey" PARA LA TABLA "usuarios" CREAR TABLA

Eliminar un usuario

Para eliminar un usuario, debe transferir sus derechos a otro y luego simplemente eliminar

REASIGNAR PROPIEDAD DE doomed_role A Successor_role; GOTA PROPIEDAD DE doomed_role;

— repite los comandos anteriores para cada base de datos en el clúster

SOLTAR PAPEL doomed_role;

Script de copia de seguridad de la tabla Postgres.

#!/bin/bash DBNAMES="web"; USUARIO="postgres"; DB_NAME="web"; NEW_OWNER="usuario_remoto"; DATE_Y=`/bin/date "+%y"` DATE_M=`/bin/date "+%m"` DATE_D=`/bin/date "+%d"` SERVICE="pgdump" BACKUP_DIR="/var/ backup_db/20$(FECHA_Y)/$(FECHA_M)/$(FECHA_D)" mkdir -p $BACKUP_DIR; para tbl en `psql -qAt -c "seleccione nombre de tabla de pg_tables donde nombre de esquema = "público";" $(DB_NAME)` \ `psql -qAt -c "seleccione nombre_secuencia de esquema_información.secuencias donde esquema_secuencia = "público";" $(DB_NAME)` \ `psql -qAt -c "seleccione nombre_tabla de information_schema.views donde table_schema = "público";" $(DB_NAME)`; hacer echo "Exportando tabla $tbl desde db $(DB_NAME) al archivo tablas3/$tbl.backup" #pg_dump --format p --verbose --table public.$tbl $(DB_NAME) > $BACKUP_DIR/$tbl pg_dump - -formato p --verbose --table public.$tbl $(DB_NAME) | gzip > $BACKUP_DIR/$tbl #pg_dump -a -d -t public.$tbl $(DB_NAME) > tablas3/$tbl.sql hecho ###################BACKUP FUNCIONES POSTGRES # Volcamos la base de datos sin fecha para poder extraer más funciones pg_dump -Fc -s -f $BACKUP_DIR/db_dump $(DB_NAME) /bin/sleep 4; # Crear una función de lista pg_restore -l $BACKUP_DIR/db_dump | grep FUNCIÓN > $BACKUP_DIR/function_list ##Cómo restaurar funciones ########################## #pg_restore -h localhost -U nombre de usuario -d nombre_base de datos - L function_list db_dump #########################

Script de copia de seguridad de la tabla Postgres. escrito en perl

Ejecutar como usuario - postgres. Si está en coronas, también desde debajo del usuario de postgresql.

#!/usr/bin/env perl uso estricto; utilizar advertencias; mi $nombre_base_datos = "biblioteca_libro"; mi $consulta =<<"EOT"; SELECT n.nspname as table_schema, c.relname as table_name FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ("r","") AND n.nspname NOT IN ("pg_catalog", "information_schema") AND n.nspname NOT LIKE "^pg_%" ; EOT $query =~ s/\n\s*/ /g; my @results = `echo "$query" | psql -At $database_name`; foreach (@results) { chomp; my ($schema, $table) = split /\|/, $_; next unless ($schema && $table); my $cmd = "pg_dump -U postgres -Fp -t $schema.$table -f $schema.$table.dump $database_name"; system($cmd); } #If you wanted to restore only a single function: ##pg_restore -U $username --dbname=$dbname --function=$functionname(args) #If you wanted to restore only a single table: ##pg_restore -U $username --dbname=$dbname --table=$tablename

Diccionarios de búsqueda de texto completo en postgresql

Copia de seguridad de la base de datos desde una máquina remota

PGPASSWORD="CONTRASEÑA" pg_dump -h $NOMBREHOST -U usuario de base de datos -Fc --verbose "database.itc-life.ru" | gzip > nombre de base de datos.gz

Copia de seguridad y restauración de tablas.

EN PostgreSQL hay dos utilidades para respaldo pg_dump y pg_dumpall. pg_dump se usa para hacer una copia de seguridad de una base de datos, pg_dumpall se usa para hacer una copia de seguridad de todas las bases de datos y del servidor en su conjunto (debe ejecutarse bajo el superusuario postgresql).

Crear una copia de seguridad de la base de datos mibd, en forma comprimida

Pg_dump -h localhost -p 5432 -U algún usuario -F c -b -v -f mydb.backup mydb

Crear una copia de seguridad de la base de datos mibd, como un archivo de texto plano, incluido el comando para crear la base de datos

Pg_dump -h localhost -p 5432 -U algún usuario -C -F p -b -v -f mydb.backup mydb

Crear una copia de seguridad de la base de datos mibd, en forma comprimida, con tablas que contienen en el nombre pagos

Pg_dump -h localhost -p 5432 -U algún usuario -F c -b -v -t *pagos* -f tablas_pagos.backup mydb

Vuelque datos de una sola tabla específica. Si necesita hacer una copia de seguridad de varias tablas, los nombres de estas tablas se enumeran usando la clave -t para cada mesa.

Pg_dump -a -t nombre_tabla -f nombre_archivo nombre_base de datos

Crear una copia de seguridad con compresión en gz

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Lista de opciones más utilizadas:

H host - host, si no se especifica, se usa servidor local fantasma.

Puerto P - puerto, si no se especifica, entonces se usa 5432 o valor de la variable de entorno PGPORT.

U - usuario, si no se especifica, entonces se utiliza el usuario actual, el valor también se puede especificar en una variable de entorno PGUSER.

A, --data-only: volca solo datos de forma predeterminada, se guardan los datos y el esquema.

B: incluir objetos grandes (blogs) en el volcado.

S, --schema-only: volca solo el esquema.

C, --create: agrega un comando para crear una base de datos.

C: agrega comandos para eliminar (soltar) objetos (tablas, vistas, etc.).

O: no agregue comandos para establecer el propietario de un objeto (tablas, vistas, etc.).

F, --format (c|t|p) — formato de salida de volcado, personalizado, alquitrán, o texto plano.

T, --table=TABLE: especifica una tabla específica para el volcado.

V, --verbose: genera información detallada.

D, --attribute-inserts - volcar usando el comando INSERTAR con una lista de nombres de propiedades.

Haga una copia de seguridad de todas las bases de datos utilizando el comando pg_dumpall.

Pg_dumpall > todo.sql # comprobando copia de seguridad grep "^[\]conectar" all.sql \connect db1 \connect db2

PostgreSQL tiene dos utilidades para recuperación bases de datos desde la copia de seguridad.

psql— restauración de copias de seguridad almacenadas en un archivo de texto sin formato;
pg_restore— restauración de copias de seguridad comprimidas (tar);

Recuperación de bases de datos y

#pg_restore -v -e -d nombrebd nombrebd.dump

Restaurar una copia de seguridad completa ignorando los errores

Psql -h localhost -U algún usuario -d nombrebd -f mydb.sql

Restaurar una copia de seguridad completa, deteniéndose en el primer error

psql -h localhost -U algún usuario --set ON_ERROR_STOP=on -f mydb.sql

para restaurar desde alquitrán-archive, primero necesitamos crear una base de datos usando CREATE DATABASE mydb; (si no se especificó la opción -C al crear la copia de seguridad) y restaurar

restauración de página --dbname=mydb --jobs=4 --verbose mydb.backup

Restaurar una copia de seguridad de una base de datos comprimida gz

Gunzip mydb.gz psql -U postgres -d mydb -f mydb

A partir de la versión 9.2, solo puede restaurar la estructura de la tabla usando la opción --section

# crear una base de datos CREAR BASE DE DATOS mydb2; # restaurando pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backup

Servicio de mesa

mesa de ANÁLISIS DE VACÍO; REINDEXAR BASE DE DATOS nombrebd; REINDICE TABLA nombre de pestaña;

Transferir el directorio de datos

Descubra el camino actual

# método 1 $ su - postgres $ psql psql > MOSTRAR directorio_datos; # método 2 $ ps hacha | grep "postgres-D"

Creemos un nuevo directorio, asignemos un usuario e inicialicemos

mkdir -p /ruta/postgresql/data chown -R postgres:postgres /ruta/postgresql su - postgres initdb -D /ruta/postgresql/data

Ahora necesitamos editar el archivo con el servicio que se inicia. postgresql

# en arch linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Entorno =PGROOT=/pathto/postgresql/ PIDFile =/pathto/postgresql/data/postmaster.pid

Limpiar una mesa

Limpiar una mesa nombre de tabla y restablecer el contador de ID.

TRUNCAR TABLA nombre de tabla REINICIAR IDENTIDAD CASCADA;

CASCADA necesario en caso nombre de tabla vinculado a otra tabla.

Eliminar NULL de un campo

ALTER TABLE películas ALTER COLUMN año DROP NOT NULL;

Ejecutando pgbouncer

su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"

Desconectar usuarios de la base de datos

SELECCIONE pg_terminate_backend(pid) DESDE pg_stat_activity DONDE datname = "mydb";`

15 comandos útiles de PostgreSQL

Hay muchos tutoriales de PostgreSQL en la web que describen los comandos básicos. Pero al profundizar en el trabajo, surgen cuestiones prácticas que requieren equipos avanzados.

Estos comandos, o fragmentos, rara vez se describen en la documentación. Veamos algunos ejemplos que son útiles tanto para desarrolladores como para administradores de bases de datos.

Obtener información sobre una base de datos

Tamaño de la base de datos

Para obtener el tamaño físico de los archivos de la base de datos (almacenamiento), utilizamos la siguiente consulta:

SELECCIONE pg_database_size(current_database());

El resultado se presentará como un número del formulario 41809016.

current_database() es una función que devuelve el nombre de la base de datos actual. En su lugar, puede ingresar el nombre en texto:

SELECCIONE pg_database_size("mi_base de datos");

Para obtener información en un formato legible por humanos, utilizamos la función pg_size_pretty:

SELECCIONE pg_size_pretty(pg_database_size(current_database()));

Como resultado, obtenemos información del tipo 40 Mb.

Lista de tablas

A veces es necesario obtener una lista de tablas de bases de datos. Para ello utilizamos la siguiente consulta:

SELECCIONE nombre_tabla DE information_schema.tables DONDE table_schema NO EN ("information_schema", "pg_catalog");

information_schema es un esquema de base de datos estándar que contiene colecciones de vistas como tablas, campos, etc. Las vistas de tabla contienen información sobre todas las tablas de la base de datos.

La siguiente consulta seleccionará todas las tablas del esquema especificado de la base de datos actual:

SELECCIONE nombre_tabla DE information_schema.tables DONDE table_schema NO EN ("information_schema", "pg_catalog") Y table_schema IN("public", "myschema");

La última condición IN se puede utilizar para especificar el nombre de un esquema específico.

Tamaño de la mesa

De manera similar a obtener el tamaño de una base de datos, el tamaño de los datos de una tabla se puede calcular usando la función adecuada:

SELECCIONAR pg_relation_size("cuentas");

La función pg_relation_size devuelve la cantidad de espacio en disco que ocupa en el disco una capa específica de una tabla o índice determinado.

Nombre de la mesa más grande

Para mostrar una lista de tablas en la base de datos actual, ordenadas por tamaño de tabla, ejecute la siguiente consulta:

SELECCIONE relname, relpages DESDE pg_class ORDENAR POR relpages DESC;

Para mostrar información sobre la tabla más grande, limitamos la consulta usando LIMIT:

SELECCIONE relname, relpages DESDE pg_class ORDENAR POR relpages DESC LIMIT 1;

relname: nombre de la tabla, índice, vista, etc.
relpages: el tamaño de la representación en disco de esta tabla en número de páginas (de forma predeterminada, una página pesa 8 KB).
pg_class es una tabla del sistema que contiene información sobre las relaciones entre tablas de bases de datos.

Lista de usuarios conectados

Para conocer el nombre, la IP y el puerto utilizado de los usuarios conectados, ejecute la siguiente consulta:

SELECCIONE nombre de datos, nombre de uso, dirección_cliente, puerto_cliente DESDE pg_stat_actividad;

Actividad del usuario

Para conocer la actividad de conexión de un usuario específico, utilice la siguiente consulta:

SELECCIONE el nombre de dat DE pg_stat_activity DONDE nombre de uso = "devuser";

Trabajar con datos y campos de tablas

Eliminar filas duplicadas

Si sucede que la tabla no tiene una clave primaria, entonces probablemente habrá duplicados entre los registros. Si para una tabla de este tipo, especialmente una grande, es necesario establecer restricciones para verificar la integridad, elimine los siguientes elementos:

líneas duplicadas,
situaciones en las que una o más columnas están duplicadas (si estas columnas están destinadas a usarse como clave principal).

Consideremos una tabla con datos de clientes, donde se duplica una fila completa (la segunda).

La siguiente consulta le ayudará a eliminar todos los duplicados:

ELIMINAR DE clientes DONDE ctid NO ESTÁ (SELECCIONAR max(ctid) DE clientes GRUPO POR clientes.*);

El campo ctid, exclusivo de cada registro, está oculto de forma predeterminada, pero está presente en todas las tablas.

La última solicitud consume muchos recursos, así que tenga cuidado al ejecutarla en un proyecto de producción.

Consideremos ahora el caso en el que los valores de los campos se repiten.

Si está permitido eliminar duplicados sin guardar todos los datos, ejecutaremos la siguiente solicitud:

ELIMINAR DE clientes DONDE ctid NO ESTÁ (SELECCIONAR max(ctid) DE clientes GRUPO POR customer_id);

Si los datos son importantes, primero debe buscar registros con duplicados:

SELECCIONE * DE clientes DONDE ctid NO ESTÁ (SELECCIONE max(ctid) DE clientes GRUPO POR customer_id);

Antes de eliminarlos, dichos registros se pueden mover a una tabla temporal o el valor de customer_id que contienen se puede reemplazar por otro.

La forma general de una solicitud para eliminar los registros descritos anteriormente es la siguiente:

ELIMINAR DE nombre_tabla DONDE ctid NO ESTÁ (SELECCIONAR máx (ctid) DE nombre_tabla GRUPO POR columna1,);

Cambiar el tipo de campo de forma segura

Puede haber dudas sobre cómo incluir dicha tarea en esta lista. Después de todo, en PostgreSQL es muy fácil cambiar el tipo de campo usando el comando ALTER. Veamos nuevamente la tabla de clientes como ejemplo.

El campo customer_id utiliza el tipo de datos de cadena varchar. Esto es un error porque se supone que este campo almacena los ID de los clientes, que están en formato de número entero. El uso de varchar no está justificado. Intentemos corregir este malentendido usando el comando ALTER:

ALTER TABLE clientes ALTER COLUMN customer_id TIPO entero;

Pero como resultado de la ejecución obtenemos un error:

ERROR: la columna "customer_id" no se puede convertir automáticamente para escribir un número entero
Estado SQL: 42804
Sugerencia: Especifique una expresión USING para realizar la conversión.

Esto significa que no puede simplemente cambiar el tipo de un campo si hay datos en la tabla. Dado que se utilizó el tipo varchar, el DBMS no puede determinar si el valor es un número entero. Aunque los datos corresponden a este tipo. Para aclarar este punto, el mensaje de error sugiere utilizar la expresión USING para convertir correctamente nuestros datos a un número entero:

ALTER TABLE clientes ALTER COLUMN customer_id TIPO entero USING (customer_id::integer);

Como resultado, todo salió sin errores:

Tenga en cuenta que al utilizar USING, además de una expresión específica, es posible utilizar funciones, otros campos y operadores.

Por ejemplo, vamos a convertir el campo customer_id nuevamente a varchar, pero con una conversión de formato de datos:

ALTER TABLE clientes ALTER COLUMN customer_id TIPO varchar USING (customer_id || "-" || first_name);

Como resultado, la tabla quedará así:

Encontrar valores "perdidos"

Tenga cuidado al utilizar secuencias como clave principal: durante la asignación, algunos elementos de la secuencia se omiten accidentalmente y, como resultado de trabajar con la tabla, algunos registros se eliminan. Estos valores se pueden volver a utilizar, pero son difíciles de encontrar en tablas grandes.

Consideremos dos opciones de búsqueda.

primera manera
Ejecutemos la siguiente consulta para encontrar el comienzo del intervalo con el valor "perdido":

SELECCIONE customer_id + 1 DE clientes mes DONDE NO EXISTE (SELECCIONE NULL DE clientes mi DONDE mi.customer_id = mes.customer_id + 1) ORDENAR POR customer_id;

Como resultado, obtenemos los siguientes valores: 5, 9 y 11.

Si necesita encontrar no solo la primera aparición, sino también todos los valores faltantes, utilizamos la siguiente consulta (¡que requiere muchos recursos!):

CON seq_max AS (SELECCIONAR max(customer_id) DE clientes), seq_min AS (SELECCIONAR min(customer_id) DE clientes) SELECCIONAR * FROM generate_series((SELECCIONAR min FROM seq_min),(SELECCIONAR max FROM seq_max)) EXCEPTO SELECCIONAR customer_id FROM clientes;

Como resultado, vemos el siguiente resultado: 5, 9 y 6.

Segunda manera
Obtenemos el nombre de la secuencia asociada con customer_id:

SELECCIONAR pg_get_serial_sequence("clientes", "customer_id");

Y encontramos todos los identificadores que faltan:

CON secuencia_info AS (SELECCIONE valor_inicial, último_valor DE "NombreEsquema". "NombreSecuencia") SELECCIONE generar_series ((sequence_info.start_value), (sequence_info.last_value)) DESDE secuencia_info EXCEPTO SELECCIONAR customer_id DE clientes;

Contar el número de filas en una tabla

El número de filas se calcula mediante la función de recuento estándar, pero se puede utilizar con condiciones adicionales.

Número total de filas de la tabla:

SELECCIONE el recuento (*) DE la tabla;

Número de filas siempre que el campo especificado no contenga NULL:

SELECCIONE el recuento (col_name) DE la tabla;

Número de filas únicas para el campo especificado:

SELECCIONE el recuento (nombre_columna distinto) DE la tabla;

Usando transacciones

Una transacción combina una secuencia de acciones en una sola operación. Su peculiaridad es que si hay un error al ejecutar una transacción, ninguno de los resultados de las acciones quedará guardado en la base de datos.

Comencemos la transacción usando el comando BEGIN.

Para revertir todas las operaciones ubicadas después de BEGIN, use el comando ROLLBACK.

Y para aplicarlo, el comando COMMIT.

Ver y finalizar consultas en ejecución

Para obtener información sobre las solicitudes, ejecute el siguiente comando:

SELECCIONE pid, edad (query_start, clock_timestamp()), nombre de uso, consulta DESDE pg_stat_activity DONDE consulta! = " " Y la consulta NO ES COMO "%pg_stat_activity%" ORDENAR POR query_start desc;

Para detener una solicitud específica, ejecute el siguiente comando, indicando la identificación del proceso (pid):

SELECCIONE pg_cancel_backend(procpid);

Para detener la solicitud, ejecute:

SELECCIONE pg_terminate_backend(procpid);

Trabajar con la configuración

Encontrar y cambiar la ubicación de una instancia de clúster

Es posible una situación en la que se configuran varias instancias de PostgreSQL en un sistema operativo, que "se asientan" en diferentes puertos. En este caso, encontrar una manera de ubicar físicamente cada instancia es una tarea bastante estresante. Para obtener esta información, ejecute la siguiente consulta para cualquier base de datos en el clúster de interés:

MOSTRAR directorio_datos;

Cambiemos la ubicación a otra usando el comando:

ESTABLECER directorio_datos en nueva_ruta_directorio;

Pero para que los cambios surtan efecto, es necesario reiniciar.

Obtener una lista de tipos de datos disponibles

Obtenemos una lista de tipos de datos disponibles usando el comando:

SELECCIONE typname, typlen de pg_type donde typtype="b";

typname: nombre del tipo de datos.
typelen: tamaño del tipo de datos.

Cambiar la configuración de DBMS sin reiniciar

La configuración de PostgreSQL se encuentra en archivos especiales como postgresql.conf y pg_hba.conf. Después de cambiar estos archivos, el DBMS necesita recibir la configuración nuevamente. Para hacer esto, se reinicia el servidor de la base de datos. Está claro que hay que hacer esto, pero en la versión de producción del proyecto, que utilizan miles de usuarios, esto es muy indeseable. Por tanto, PostgreSQL tiene una función que permite aplicar cambios sin reiniciar el servidor:

SELECCIONE pg_reload_conf();

Pero, lamentablemente, esto no se aplica a todos los parámetros. En algunos casos, es necesario reiniciar para que se aplique la configuración.

Analizamos comandos que ayudarán a simplificar el trabajo de los desarrolladores y administradores de bases de datos que utilizan PostgreSQL. Pero estos no son todos los métodos posibles. Si ha encontrado tareas interesantes, escríbalas en los comentarios. ¡Compartamos experiencias útiles!

Última actualización: 17/03/2018

Para crear tablas, utilice el comando CREATE TABLE, seguido del nombre de la tabla. También puede utilizar varios operadores con este comando que definen las columnas de la tabla y sus atributos. La sintaxis general para crear una tabla es la siguiente:

CREAR TABLA nombre_tabla (nombre_columna1 tipo_datos atributos_columna1, nombre_columna2 tipo_datos atributos_columna2, .................................... . ......... nombre_columnaN tipo_datos atributos_columnaN, atributos_tabla);

Después del nombre de la tabla, la especificación de todas las columnas aparece entre paréntesis. Además, para cada columna deberás indicar el nombre y tipo de dato que representará. El tipo de datos determina qué datos (números, cadenas, etc.) puede contener una columna.

Por ejemplo, creemos una tabla en la base de datos usando pgAdmin. Para hacer esto, primero seleccione la base de datos de destino en pgAdmin, haga clic derecho sobre ella y seleccione Herramienta de consulta... en el menú contextual:

Después de esto, se abrirá un campo para ingresar el código SQL. Además, la tabla se creará específicamente para la base de datos para la que abrimos este campo de entrada SQL.

CREAR TABLA clientes (Id. CLAVE PRIMARIA DE SERIE, Nombre CARACTER VARIADO (30), Apellido CARACTER VARIADO (30), Correo electrónico CARACTER VARIADO (30), Edad INTEGER);

En este caso, se definen cinco columnas en la tabla Clientes: Id, Nombre, Apellido, Edad, Correo electrónico. La primera columna, Id, representa el ID del cliente, sirve como clave principal y, por tanto, es de tipo SERIAL. De hecho, esta columna almacenará el valor numérico 1, 2, 3, etc., que aumentará automáticamente en uno por cada nueva fila.

Las siguientes tres columnas representan el nombre, apellido y dirección de correo electrónico del cliente y son de tipo CHARACTER VARYING(30), lo que significa que son una cadena de no más de 30 caracteres.

La última columna, Edad, representa la edad del usuario y es de tipo INTEGER, lo que significa que almacena números.

Y luego de ejecutar este comando, la tabla de clientes se agregará a la base de datos seleccionada.