PostgreSQL: Empezando

Cómo empezar con PostgreSQL

👋 ¡Bienvenido a la documentación de Stackhero!

Stackhero ofrece una solución nube PostgreSQL lista para usar que proporciona una serie de beneficios, incluyendo:

• Conexiones y transferencias de datos ilimitadas.
• Interfaz web PgAdmin incluida.
• Muchos módulos incluidos como PostGIS, TimescaleDB y PgVector.
• Actualizaciones sin esfuerzo con solo un clic.
• Rendimiento óptimo y seguridad robusta impulsada por una VM privada y dedicada.

Ahorre tiempo y simplifique su vida: ¡solo toma 5 minutos probar la solución de nube hosting PostgreSQL de Stackhero!

Uso de la CLI psql de PostgreSQL

Puede gestionar PostgreSQL con su CLI oficial psql. Está disponible para su instalación en su ordenador, permitiéndole gestionar su servicio PostgreSQL de forma remota. Alternativamente, puede ejecutarlo a través de Docker. Este método es a menudo preferido porque evita instalar psql directamente en su ordenador y facilita el cambio entre versiones.

Para acceder a psql usando Docker, ejecute el siguiente comando (asegúrese de reemplazar 18 con el número de versión principal de su servicio PostgreSQL):

docker run -v $(pwd):/mnt -it postgres:18-alpine /bin/bash

Luego conéctese a su servicio PostgreSQL usando:

cd /mnt
psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=admin

Creación de un usuario y base de datos en PostgreSQL

Por defecto, se crea un usuario admin con derechos administrativos. Es una buena práctica crear un usuario y una base de datos dedicados para cada proyecto que planee alojar.

Uso de la interfaz web de PgAdmin

Para usar la interfaz web de PgAdmin, abra su dominio PostgreSQL con HTTPS (por ejemplo, https://<XXXXXX>.stackhero-network.com). Inicie sesión con admin como nombre de usuario y la contraseña que configuró en su servicio (visible en su panel de Stackhero).

Creación de un usuario

1. Vaya a Servers / PostgreSQL, haga clic derecho en Login/Group Roles, y seleccione Create / Login/Group Role:

   Crear un usuario en PostgreSQL usando PgAdmin

2. Establezca el nombre de inicio de sesión:

   Definir el inicio de sesión del usuario

3. Establezca una contraseña segura para evitar ataques de fuerza bruta:

   Definir la contraseña del usuario

4. Finalmente, asegúrese de que solo el privilegio "Can login" esté seleccionado:

   Definir los derechos del usuario

Haga clic en el botón "Save" para crear su usuario.

Creación de una base de datos

1. Vaya a Servers / postgresql, haga clic derecho en Databases, y seleccione Create / Database...:

   Crear una base de datos usando PgAdmin

Es una buena práctica usar el mismo nombre para la base de datos y el usuario. Por ejemplo, si su proyecto se llama "superWebsite", considere crear un usuario llamado "superWebsite" y una base de datos llamada "superWebsite".

1. Establezca el nombre de la base de datos y elija el propietario (el usuario que acaba de crear):

   Definir el nombre y propietario de la base de datos

Su base de datos ahora está creada.

Uso de la CLI psql

Creación de un usuario

Para crear un usuario en PostgreSQL usando la CLI psql, ejecute la siguiente consulta SQL:

CREATE ROLE "myProject" WITH
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT
  NOREPLICATION
  CONNECTION LIMIT -1
  PASSWORD 'secretPassword';

No olvide reemplazar myProject con el nombre de su proyecto y secretPassword con una contraseña segura. También es una buena práctica usar el nombre de su proyecto como nombre de inicio de sesión y de base de datos. Si su proyecto se llama "superWebsite", considere crear un usuario llamado "superWebsite" y una base de datos llamada "superWebsite".

Puede generar una contraseña segura con esta línea de comando: openssl rand -base64 24 | tr -d '\n' | cut -c1-32

Creación de una base de datos

Para crear una base de datos en PostgreSQL usando la CLI psql, ejecute la siguiente consulta SQL:

CREATE DATABASE "myProject"
  WITH
  OWNER = "myProject"
  ENCODING = 'UTF8'
  CONNECTION LIMIT = -1
  IS_TEMPLATE = false;

Es una buena práctica usar el mismo nombre para la base de datos y el usuario. Por ejemplo, si su proyecto se llama "superWebsite", considere crear un usuario llamado "superWebsite" y una base de datos llamada "superWebsite".

Importación de datos desde su ordenador a PostgreSQL

Una de las formas más sencillas de importar datos desde su ordenador a su instancia de PostgreSQL es usar la CLI de PostgreSQL. En su ordenador, ejecute el siguiente comando (reemplace <DB_NAME> con el nombre de su base de datos y data.pgsql con el nombre de su archivo SQL):

psql \
  --host=<XXXXXX>.stackhero-network.com \
  --username=admin \
  --port=<PORT> \
  --dbname=<DB_NAME> \
  < data.pgsql

Exportación de datos desde PostgreSQL a su ordenador

Exportar datos desde su instancia de PostgreSQL a su ordenador es igual de sencillo usando la CLI de PostgreSQL.

1. Para exportar toda la base de datos (reemplace <DB_NAME> con el nombre de su base de datos):

   pg_dump \
     --host=<XXXXXX>.stackhero-network.com \
     --username=admin \
     --port=<PORT> \
     --dbname=<DB_NAME> \
     > data.pgsql
   

2. Para exportar una sola tabla (reemplace <DB_NAME> con el nombre de su base de datos y <TABLE_NAME> con el nombre de su tabla):

   pg_dump \
     --host=<XXXXXX>.stackhero-network.com \
     --username=admin \
     --port=<PORT> \
     --dbname=<DB_NAME> \
     --table=<TABLE_NAME> \
     > data.pgsql
   

Uso de PostgreSQL con Node.js

Para conectar PostgreSQL usando Node.js y el paquete pg (también conocido como node-postgres), puede considerar el siguiente ejemplo. Note el argumento ssl, que es importante para asegurar el cifrado TLS:

const { Client } = require('pg');

(async () => {
  const pg = new Client({
    host: '<XXXXXX>.stackhero-network.com',
    port: <PORT>,
    user: '<USERNAME>',
    password: '<PASSWORD>',
    database: '<DATABASE>',
    ssl: {}
  });

  await pg.connect();

  const result = await pg.query('SELECT 1');

  await pg.end();
})().catch(error => {
  console.error('');
  console.error('🐞 ¡Ocurrió un error!');
  console.error(error);
  process.exit(1);
});

Un ejemplo completo usando PostgreSQL con Node.js y la biblioteca pg (y async/await) está disponible en este repositorio Git: https://github.com/stackhero-io/postgresqlGettingStarted.

Uso de PostgreSQL con Node.js/TypeORM

Para conectarse a PostgreSQL desde Node.js usando TypeORM, configure la bandera ssl a true como se muestra en el ejemplo a continuación:

createConnection({
  type: 'postgres',
  host: '<XXXXXX>.stackhero-network.com',
  port: <PORT>,
  username: '<USERNAME>',
  password: '<PASSWORD>',
  database: '<DATABASE>',
  extra: {
    ssl: true
  }
});

Uso de PostgreSQL con Django

Si no está ya instalado, instale el módulo psycopg, que se usará para conectarse a PostgreSQL:

pip install psycopg

En este paso inicial, almacenará la contraseña directamente en el archivo settings.py. Este método es solo para pruebas porque no es seguro. Más adelante en esta documentación, encontrará un ejemplo de buena práctica.

Abra el archivo settings.py y agregue la siguiente configuración:

DATABASES = {
  'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'HOST': '<XXXXXX>.stackhero-network.com',
    'PORT': <PORT>,
    'OPTIONS': {
      'sslmode': 'require',
    },
    'NAME': 'admin',
    'USER': 'admin',
    'PASSWORD': '<ADMIN_PASSWORD>'
  }
}

Tenga cuidado: este ejemplo no se recomienda para producción y está destinado solo para fines de prueba.

Una vez que su conexión funcione, puede adoptar un método más seguro para almacenar credenciales. El siguiente ejemplo usa django-environ y almacena credenciales en un archivo .env.

1. Instale django-environ:

   pip install django-environ
   

2. Abra el archivo settings.py y modifíquelo como sigue:

   import environ
   env = environ.Env()
   environ.Env.read_env()
   
   DATABASES = {
     'default': {
       'ENGINE': 'django.db.backends.postgresql',
       'HOST': env('STACKHERO_POSTGRESQL_HOST'),
       'PORT': <PORT>,
       'OPTIONS': {
         'sslmode': 'require',
       },
       'NAME': 'admin',
       'USER': 'admin',
       'PASSWORD': env('STACKHERO_POSTGRESQL_ADMIN_PASSWORD')
     }
   }
   

3. Abra o cree el archivo .env en el mismo directorio que settings.py y agregue:

   STACKHERO_POSTGRESQL_HOST=<XXXXXX>.stackhero-network.com
   STACKHERO_POSTGRESQL_ADMIN_PASSWORD=<ADMIN_PASSWORD>
   

4. Finalmente, agregue .env a su archivo .gitignore para asegurarse de que sus credenciales no se almacenen en su repositorio Git:

echo ".env" >> .gitignore

Activación de la extensión PostGIS de PostgreSQL

La extensión PostGIS está incluida con nuestro servicio PostgreSQL. Debe activarla en cada base de datos donde planee usarla.

Para activar la extensión, conéctese a su base de datos y ejecute esta consulta:

CREATE EXTENSION postgis;

Puede verificar que PostGIS está funcionando comprobando su versión:

SELECT PostGIS_Full_Version();

Alternativamente, recupere una lista de todas las extensiones PostGIS instaladas:

SELECT * FROM pg_extension WHERE extname LIKE 'postgis%';

Si es necesario, puede agregar otras extensiones. Sin embargo, se recomienda encarecidamente que no active extensiones que no vaya a usar:

-- Activar PostGIS
CREATE EXTENSION postgis;

-- Activar soporte raster
CREATE EXTENSION postgis_raster;

-- Activar Topología
CREATE EXTENSION postgis_topology;

-- Coincidencia difusa necesaria para Tiger
CREATE EXTENSION fuzzystrmatch;

-- Estandarizador basado en reglas
CREATE EXTENSION address_standardizer;

-- Conjunto de datos de ejemplo de reglas
CREATE EXTENSION address_standardizer_data_us;

-- Activar Geocodificador US Tiger
CREATE EXTENSION postgis_tiger_geocoder;

Precaución: ¡no active PostGIS en la base de datos postgres!

Desactivación de la extensión PostGIS de PostgreSQL

Para eliminar PostGIS de una base de datos, conéctese a la base de datos relevante y ejecute la siguiente consulta:

DROP EXTENSION postgis;

Activación de la extensión PgVector

Activar la extensión PgVector en Stackhero es simple. Ejecute la siguiente consulta:

CREATE EXTENSION vector;

Activación de la extensión TimescaleDB

Activar la extensión TimescaleDB en Stackhero es simple. Ejecute la siguiente consulta:

CREATE EXTENSION timescaledb;

Seguimiento de estadísticas de planificación y ejecución SQL (pg_stat_statements)

Para habilitar el soporte para pg_stat_statements, primero actívelo en su configuración de PostgreSQL a través del panel de Stackhero. Luego, habilite la extensión para su base de datos actual ejecutando:

CREATE EXTENSION pg_stat_statements;

Para más información, por favor consulte la documentación oficial de PostgreSQL.

Configuración de PostgreSQL con Skyvia

Usar Skyvia con Stackhero para PostgreSQL es sencillo. Asegure una conexión segura configurando los siguientes ajustes:

1. Establezca Protocol en SSL
2. Establezca Port en su puerto de PostgreSQL
3. Fuerce el cifrado estableciendo SSL Mode en Require
4. Seleccione un SSL TLS Protocol de 1.2 o superior

Para más detalles sobre la configuración de Skyvia, por favor consulte la documentación oficial de Skyvia.

Ejemplo de configuración de Skyvia con Stackhero para PostgreSQL