PostgreSQL | Lade

Lade officially supports deploying PostgreSQL databases.

Supported Versions

The following PostgreSQL versions are supported and receive security updates:

Provisioning PostgreSQL

A PostgreSQL addon can be provisioned with the CLI:

$ lade addons create postgres --name mydb

You will be prompted to select an available plan, region, and version.

Attaching to Your App

Your PostgreSQL addon can be attached to your app with the CLI:

$ lade addons attach mydb --app myapp

When your addon is attached, the DATABASE_URL and POSTGRES_URL environment variables will be added to your app.

You can confirm these environment variables were added to your app with the lade env list command.

Accessing Your Database

To access your database remotely, you will need to enable public access on your addon. You can view settings with the lade addons show command. The addon URI will follow the format:

postgresql://<user>:<password>@<host>:<port>/<dbname>

You can access your remote database with the psql command:

$ psql <addon URI>
psql (16, server 16)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

user=#

PostgreSQL Extensions

You can enable required PostgreSQL extensions with the CREATE EXTENSION command:

$ psql <addon URI>
psql (16, server 16)
Type "help" for help.

user=# CREATE EXTENSION citext;
CREATE EXTENSION
user=# CREATE EXTENSION hstore;
CREATE EXTENSION

Available Extensions

The following extensions can be enabled for your database:

adminpack: administrative functions for PostgreSQL
amcheck: functions for verifying relation integrity
autoinc: functions for autoincrementing fields
bloom: bloom access method - signature file based index
btree_gin: support for indexing common datatypes in GIN
btree_gist: support for indexing common datatypes in GiST
citext: data type for case-insensitive character strings
cube: data type for multidimensional cubes
dblink: connect to other PostgreSQL databases from within a database
dict_int: text search dictionary template for integers
dict_xsyn: text search dictionary template for extended synonym processing
earthdistance: calculate great-circle distances on the surface of the Earth
file_fdw: foreign-data wrapper for flat file access
fuzzystrmatch: determine similarities and distance between strings
hstore: data type for storing sets of (key, value) pairs
insert_username: functions for tracking who changed a table
intagg: integer aggregator and enumerator (obsolete)
intarray: functions, operators, and index support for 1-D arrays of integers
isn: data types for international product numbering standards
lo: Large Object maintenance
ltree: data type for hierarchical tree-like structures
moddatetime: functions for tracking last modification time
pageinspect: inspect the contents of database pages at a low level
pg_buffercache: examine the shared buffer cache
pg_freespacemap: examine the free space map (FSM)
pg_prewarm: prewarm relation data
pg_stat_statements: track execution statistics of all SQL statements executed
pg_trgm: text similarity measurement and index searching based on trigrams
pg_visibility: examine the visibility map (VM) and page-level visibility info
pgcrypto: cryptographic functions
pgrowlocks: show row-level locking information
pgstattuple: show tuple-level statistics
postgres_fdw: foreign-data wrapper for remote PostgreSQL servers
refint: functions for implementing referential integrity (obsolete)
seg: data type for representing line segments or floating-point intervals
sslinfo: information about SSL certificates
tablefunc: functions that manipulate whole tables, including crosstab
tcn: Triggered change notifications
tsm_system_rows: TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time: TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent: text search dictionary that removes accents
uuid-ossp: generate universally unique identifiers (UUIDs)
xml2: XPath querying and XSLT