Drupal on PostgreSQL using an external PostgreSQL database

This tutorial assumes you have already installed a Drupal 9/10 instance on a PostgreSQL database and have not installed xnttdb module yet. In this tutorial, the PostgreSQL user used by Drupal is "drupal" and the user used for the external PostgreSQL database is "pgother" (already created). We will create a PostgreSQL database and allow the "pgother" account to use it. We will use the default "public" schema. We will create 2 tables in that new database, one for "teams" and one for "people". We will insert 2 default teams and 6 default people as datasets for testing purpose.

1. Setup your secondary database

Connect to your secondary PostgreSQL database with your "pgsql" account and create a new database:

CREATE DATABASE staff_db OWNER pgother;

Then connect to your secondary new new PostgreSQL database "staff_db" with your account "pgother" and create tables:

CREATE TABLE teams(
  tid         SERIAL PRIMARY KEY NOT NULL,
  name        TEXT               NOT NULL,
  description TEXT               NOT NULL
);

CREATE TABLE people(
  ppid          SERIAL PRIMARY KEY NOT NULL,
  name          TEXT               NOT NULL,
  date_of_birth TIMESTAMP          NOT NULL,
  grade         INT                NOT NULL,
  tid           INT                NULL DEFAULT NULL,
  CONSTRAINT fk_team
    FOREIGN KEY(tid) 
	  REFERENCES teams(tid)
    ON DELETE SET NULL
);

INSERT INTO teams(name, description) VALUES
  ('Team 1', 'The first team'),
  ('Backup', 'The backup team')
;

INSERT INTO people(name, date_of_birth, grade, tid) VALUES
  ('John Doe', '1975-09-14', 10, 1),
  ('Jane Doe', '1976-11-08', 9 , 1),
  ('Jack Martin', '1983-02-25', 5 , NULL),
  ('Kate Foo', '1980-05-16', 10 , 1),
  ('John Smith', '1987-01-30', 4 , 2),
  ('Rebecca Smith', '1988-06-01', 5 , 2)
;

Now edit you Drupal site configuration file "settings.php" and add this database connection details:

// Somewhere in your settings.php, you should have something like that:
$databases['default']['default'] = array (
  'database' => 'drupal_db',
  'username' => 'drupal',
  'password' => 'Drupal1234!',
  'prefix' => '',
  'host' => '192.168.0.13',
  'port' => '5432',
  'namespace' => 'Drupal\\pgsql\\Driver\\Database\\pgsql',
  'driver' => 'pgsql',
  'autoload' => 'core/modules/pgsql/src/Driver/Database/pgsql/',
);
// The lines above should be already there with the appropriate values.

// Add the new lines below (to adjust) for your PostgreSQL extra-connection:
$databases['extra_pgsql']['default'] = array (
  'database' => 'staff_db',
  'username' => 'pgother',
  'password' => 'Drupal1234!',
  'prefix' => '',
  'host' => '192.168.0.42',
  'port' => '5432',
  'namespace' => 'Drupal\\pgsql\\Driver\\Database\\pgsql',
  'driver' => 'pgsql',
  'autoload' => 'core/modules/pgsql/src/Driver/Database/pgsql/',
);

Of course, you will need to adjust these settings to fit your configuration (maybe different user credentials, server IP, etc.).

2. Install and enable modules

From here, you can follow the "Drupal on PostgreSQL using a secondary PostgreSQL schema (same server connection)" tutorial starting at "2. Install and enable modules" with only one important change at step 3.1: the "Secondary database key name (optional)" will be set to "extra_pgsql"  and the "Schema name" to "public", as shown below: