pgAdmin 4

https://www.pgadmin.org/docs/pgadmin4/latest/
Admin
pgAdmin 4 is the leading Open Source management tool for Postgres, providing a powerful graphical...
Tokens:81,236
Snippets:1,357
Trust Score:10
Update:2 months ago
Show doc for...
Tokens:
Raw
### Install pgAgent service on Windows

Source: https://www.pgadmin.org/docs/pgadmin4/latest/pgagent_install.html

Example command to install the pgAgent service using specific credentials and connection parameters.

```cmd
"C:\Program Files\pgAgent\bin\pgAgent" INSTALL pgAgent -u postgres -p secret hostaddr=127.0.0.1 dbname=postgres user=postgres
```

--------------------------------

### Run pgAgent on Unix

Source: https://www.pgadmin.org/docs/pgadmin4/latest/pgagent_install.html

Example command to start the pgAgent daemon with a specific connection string.

```bash
/path/to/pgagent hostaddr=127.0.0.1 dbname=postgres user=postgres
```

--------------------------------

### Initialize the configuration database

Source: https://www.pgadmin.org/docs/pgadmin4/latest/server_deployment.html

Execute the setup script to create the necessary configuration database for pgAdmin 4.

```bash
# python setup.py setup-db
```

--------------------------------

### Storage Directory Path Examples

Source: https://www.pgadmin.org/docs/pgadmin4/latest/release_notes_4_4.html

Examples of the per-user storage directory path structure before and after the fix for Issue #3887.

```text
/var/lib/pgadmin4/storage/username/
```

```text
/var/lib/pgadmin4/storage/username_example.com/
```

--------------------------------

### Manage users via command line

Source: https://www.pgadmin.org/docs/pgadmin4/latest/user_management.html

Examples of using the pgadmin4-cli tool to add users and retrieve preferences.

```bash
pgadmin4-cli add-user user1@gmail.com password --role 1 pgadmin4-cli get-prefs
```

--------------------------------

### Run pgAdmin with uWSGI

Source: https://www.pgadmin.org/docs/pgadmin4/latest/server_deployment.html

Command to start pgAdmin using uWSGI directly.

```bash
uwsgi --http-socket 0.0.0.0:80 \
      --processes 1 \
      --threads 25 \
      --chdir /usr/lib/python3.7/dist-packages/pgadmin4/ \
      --mount /=pgAdmin4:app
```

--------------------------------

### Server Configuration JSON Structure

Source: https://www.pgadmin.org/docs/pgadmin4/latest/import_export_servers.html

Example demonstrating both a minimally defined server and a fully configured server with advanced options like SSH tunneling and connection parameters.

```json
{
    "Servers": {
        "1": {
            "Name": "Minimally Defined Server",
            "Group": "Server Group 1",
            "Port": 5432,
            "Username": "postgres",
            "Host": "localhost",
            "MaintenanceDB": "postgres",
            "ConnectionParameters": {
                "sslmode": "prefer",
                "connect_timeout": 10
            }
        },
        "2": {
            "Name": "Fully Defined Server",
            "Group": "Server Group 2",
            "Host": "host.domain.com",
            "Port": 5432,
            "MaintenanceDB": "postgres",
            "Username": "postgres",
            "Role": "my_role_name",
            "Comment": "This server has every option configured in the JSON",
            "DBRestriction": "live_db test_db",
            "Shared": false,
            "SharedUsername": "postgres",
            "BGColor": "#ff9900",
            "FGColor": "#000000",
            "Service": "postgresql-10",
            "UseSSHTunnel": 1,
            "TunnelHost": "192.168.1.253",
            "TunnelPort": 22,
            "TunnelUsername": "username",
            "TunnelAuthentication": 1,
            "TunnelIdentityFile": "/Users/<user>/.ssh/id_rsa.pub",
            "TunnelKeepAlive": 30,
            "PasswordExecCommand": "echo 'test'",
            "PasswordExecExpiration": 100,
            "KerberosAuthentication": true,
            "ConnectionParameters": {
                "sslmode": "prefer",
                "connect_timeout": 10,
                "passfile": "/Users/<user>/.pgpass",
                "sslcert": "/Users/<user>/.ssh/cert"
            },
            "Tags": [
                {
                    "color": "#EC0BB4",
                    "text": "Development"
                }
            ],
            "PostConnectionSQL": "set timezone='America/New_York'"
        }
    }
}
```

--------------------------------

### Install pl/pgsql language

Source: https://www.pgadmin.org/docs/pgadmin4/latest/pgagent_install.html

Ensure the procedural language is available in the database before using pgAgent.

```sql
CREATE LANGUAGE plpgsql;
```

--------------------------------

### Run pgAdmin with Gunicorn

Source: https://www.pgadmin.org/docs/pgadmin4/latest/server_deployment.html

Command to start pgAdmin using Gunicorn directly.

```bash
gunicorn  --bind 0.0.0.0:80 \
          --workers=1 \
          --threads=25 \
          --chdir /usr/lib/python3.7/dist-packages/pgadmin4 \
          pgAdmin4:app
```

--------------------------------

### Create pgAgent extension

Source: https://www.pgadmin.org/docs/pgadmin4/latest/pgagent_install.html

Execute this command in the maintenance database to install the pgAgent extension.

```sql
CREATE EXTENSION pgagent;
```

--------------------------------

### Configuring Recreate deployment strategy

Source: https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

Set the deployment strategy to Recreate to ensure the old pod is terminated before the new one starts, avoiding schema misalignment.

```yaml
spec:
  strategy:
    type: Recreate   # not RollingUpdate
```

--------------------------------

### Add User via setup.py

Source: https://www.pgadmin.org/docs/pgadmin4/latest/user_management.html

Use the add-user command to create a new user, optionally specifying roles and account status.

```bash
/path/to/python /path/to/setup.py add-user user1@gmail.com password

# to specify a role, either you can use --admin for Administrator role or provide the
# role using --role. If both are provided --admin will be used:

/path/to/python /path/to/setup.py add-user user1@gmail.com password --admin
/path/to/python /path/to/setup.py add-user user1@gmail.com password --role Users

# to specify user's status

/path/to/python /path/to/setup.py add-user user1@gmail.com password --active
/path/to/python /path/to/setup.py add-user user1@gmail.com password --inactive
```

--------------------------------

### Retrieve User Details via setup.py

Source: https://www.pgadmin.org/docs/pgadmin4/latest/user_management.html

Use the get-users command to list all users or retrieve details for a specific user.

```bash
# to list all the users:
/path/to/python /path/to/setup.py get-users

# to get the user's details:
/path/to/python /path/to/setup.py get-users --username user1@gmail.com
```

--------------------------------

### SQL Filter Example

Source: https://www.pgadmin.org/docs/pgadmin4/latest/editgrid.html

Example of a SQL filtering criteria string to be used in the WHERE clause.

```sql
id > 25 AND created > '2018-01-01'
```

--------------------------------

### Add External User via setup.py

Source: https://www.pgadmin.org/docs/pgadmin4/latest/user_management.html

Use the add-external-user command to register users authenticated via external sources like LDAP.

```bash
/path/to/python /path/to/setup.py add-external-user user1@gmail.com ldap

# to specify an email:

/path/to/python /path/to/setup.py add-external-user ldapuser ldap --email user1@gmail.com

# to specify a role, either you can use --admin for Administrator role or provide the
# role using --role. If both are provided --admin will be used:

/path/to/python /path/to/setup.py add-external-user ldapuser ldap  --admin
/path/to/python /path/to/setup.py add-external-user ldapuser ldap  --role Users

# to specify user's status

/path/to/python /path/to/setup.py add-external-user user1@gmail.com ldap --active
/path/to/python /path/to/setup.py add-external-user user1@gmail.com ldap --inactive
```

--------------------------------

### Retrieve all preferences

Source: https://www.pgadmin.org/docs/pgadmin4/latest/preferences.html

Use the get-prefs command to list all available preference keys and their current values.

```bash
/path/to/python /path/to/setup.py get-prefs
```

--------------------------------

### Launch pgAdmin for subdirectory hosting with Traefik v2

Source: https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

Deployment command using updated Traefik v2 labels for host and path prefix routing.

```bash
docker pull dpage/pgadmin4
docker run --name "pgadmin4" \
    -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \
    -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \
    -e "SCRIPT_NAME=/pgadmin4" \
    -l "traefik.frontend.pgadmin4.rule=Host(`host.example.com`) && PathPrefix(`/pgadmin4`)" \
    -d dpage/pgadmin4
```

--------------------------------

### Marking strings for translation

Source: https://www.pgadmin.org/docs/pgadmin4/latest/translations.html

Examples of marking strings in Python, Jinja templates, and JavaScript using gettext and _() functions.

```python
errormsg = gettext('No server group name was specified')
```

```jinja
<input type="submit" value="{{ _('Change Password') }}">
```

```jinja
<title>{{ _('%(appname)s Password Change', appname=config.APP_NAME) }}</title>
```

```javascript
define(['sources/gettext', ...], function(gettext, ...){
    ...
    var alert = alertify.prompt(
        gettext('Password Change'),
        gettext('New password for %(userName)s', {userName: 'jsmith' }),
        ...
    )
})
```

--------------------------------

### Windows pgAgent service usage

Source: https://www.pgadmin.org/docs/pgadmin4/latest/pgagent_install.html

Displays the command line syntax for installing, removing, or debugging the pgAgent service on Windows.

```text
Usage:
  pgAgent REMOVE <serviceName>
  pgAgent INSTALL <serviceName> [options] <connect-string>
  pgAgent DEBUG [options] <connect-string>

  options:
    -u <user or DOMAIN\user>
    -p <password>
    -d <displayname>
    -t <poll time interval in seconds (default 10)>
    -r <retry period after connection abort in seconds (>=10, default 30)>
    -l <logging verbosity (ERROR=0, WARNING=1, DEBUG=2, default 0)>
```

--------------------------------

### Launch pgAdmin for subdirectory hosting with Traefik v1

Source: https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

Deployment command using SCRIPT_NAME and Traefik labels to route traffic via a path prefix.

```bash
docker pull dpage/pgadmin4
docker run --name "pgadmin4" \
    -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \
    -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \
    -e "SCRIPT_NAME=/pgadmin4" \
    -l "traefik.frontend.rule=PathPrefix:/pgadmin4" \
    -d dpage/pgadmin4
```

--------------------------------

### Define AKS ServiceAccount and Deployment for Workload Identity

Source: https://www.pgadmin.org/docs/pgadmin4/latest/oauth2.html

Example Kubernetes manifests for setting up a ServiceAccount and Deployment configured for Azure Workload Identity.

```yaml
apiVersion: v1
kind: ServiceAccount
metadata:
  name: pgadmin
  namespace: pgadmin
  annotations:
    azure.workload.identity/client-id: "<Application (client) ID>"
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgadmin
  namespace: pgadmin
spec:
  template:
    metadata:
      labels:
        azure.workload.identity/use: "true"
    spec:
      serviceAccountName: pgadmin
```

--------------------------------

### Deploy pgAdmin container with Docker

Source: https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html

Starts a pgAdmin container mapping host port 5050 to container port 80 with default credentials.

```bash
docker pull dpage/pgadmin4
docker run -p 5050:80 \
    -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \
    -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \
    -d dpage/pgadmin4
```

--------------------------------

### Configure Binary Utility Paths

Source: https://www.pgadmin.org/docs/pgadmin4/latest/config_py.html

Sets default and fixed paths for database binary utilities like pg_dump and pg_restore.

```python
DEFAULT_BINARY_PATHS = {
    "pg": "",
    "pg-13": "",
    "pg-14": "",
    "pg-15": "",
    "pg-16": "",
    "pg-17": "",
    "pg-18": "",
    "ppas": "",
    "ppas-13": "",
    "ppas-14": "",
    "ppas-15": "",
    "ppas-16": "",
    "ppas-17": "",
    "ppas-18": ""
}

FIXED_BINARY_PATHS = {
    "pg": "",
    "pg-13": "",
    "pg-14": "",
    "pg-15": "",
    "pg-16": "",
    "pg-17": "",
    "pg-18": "",
    "ppas": "",
    "ppas-13": "",
    "ppas-14": "",
    "ppas-15": "",
    "ppas-16": "",
    "ppas-17": "",
    "ppas-18": ""
}
```

--------------------------------

### Configure Server Hosting and Security

Source: https://www.pgadmin.org/docs/pgadmin4/latest/config_py.html

Settings for network binding, CSRF headers, and HTTP security headers like X-Frame-Options and CSP.

```python
WTF_CSRF_HEADERS = ['X-pgA-CSRFToken']
DESKTOP_USER = APP_DEFAULT_EMAIL
DEFAULT_SERVER = '127.0.0.1'
DEFAULT_SERVER_PORT = 5050
WEB_SERVER = 'Python'
X_FRAME_OPTIONS = "SAMEORIGIN"
CROSS_ORIGIN_OPENER_POLICY = "same-origin"
CONTENT_SECURITY_POLICY = "default-src ws: http: data: blob: 'unsafe-inline'" \
                          " 'unsafe-eval';"
STRICT_TRANSPORT_SECURITY_ENABLED = False
STRICT_TRANSPORT_SECURITY = "max-age=31536000; includeSubDomains"
X_CONTENT_TYPE_OPTIONS = "nosniff"
```

--------------------------------

### Configure development paths in dev_config.json

Source: https://www.pgadmin.org/docs/pgadmin4/latest/desktop_deployment.html

Use this file to specify custom paths for the Python executable and the pgAdmin application file when working outside a standard installation.

```json
{
    "pythonPath": "/path/to/python.exe",
    "pgadminFile": "/path/to/pgAdmin4.py"
}
```

--------------------------------

### Importing pgAdmin Servers

Source: https://www.pgadmin.org/docs/pgadmin4/latest/import_export_servers.html

Commands to import server configurations from a JSON file, including options to replace existing servers or target a specific configuration database.

```bash
/path/to/python /path/to/setup.py load-servers input_file.json

# or, to replace the list of servers with the newly imported one:

/path/to/python /path/to/setup.py load-servers input_file.json --replace

# or, to specify a non-default user name and auth source (the default is Internal) to own the new servers:

/path/to/python /path/to/setup.py load-servers input_file.json --user user@example.com

# to specify a pgAdmin config DB file:

/path/to/python /path/to/setup.py load-servers input_file.json --sqlite-path /path/to/pgadmin4.db
```

--------------------------------

### Configure Server Authentication and Limits

Source: https://www.pgadmin.org/docs/pgadmin4/latest/config_py.html

Settings for password execution commands, server tag limits, and log batch sizes.

```python
ENABLE_SERVER_PASS_EXEC_CMD = False
MAX_SERVER_TAGS_ALLOWED = 5
ON_DEMAND_LOG_COUNT = 10000
```

--------------------------------

### Configure Application Performance and Behavior

Source: https://www.pgadmin.org/docs/pgadmin4/latest/config_py.html

Settings for asynchronous request handling, SQLAlchemy tracking, and UI pagination.

```python
THREADED_MODE = True
SQLALCHEMY_TRACK_MODIFICATIONS = False
DATA_RESULT_ROWS_PER_PAGE = 1000
```
Tokens:
Raw