Postgres.app

https://postgresapp.com/documentation/
Admin
Postgres.app is a native macOS application that bundles PostgreSQL and PostGIS, letting you install,...
Tokens:12,842
Snippets:121
Trust Score:9.7
Update:3 hours ago
Show doc for...
Tokens:
Raw
### Start PostgreSQL server manually

Source: https://postgresapp.com/documentation/troubleshooting.html

Command to start the PostgreSQL server manually for debugging. Replace 'DATA DIRECTORY' with the actual data directory and 'PORT' with the port number. Useful to see detailed error messages when the server fails to start.

```bash
/Applications/Postgres.app/Contents/Versions/latest/bin/postgres -D "DATA DIRECTORY" -p PORT
```

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

### Restore globals with psql -f

Source: https://postgresapp.com/documentation/migrating-data.html

After starting the new server, run this command to restore the globals from globals.sql.

```bash
psql -f globals.sql
```

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

### MacPorts: List installed ports with sudo port installed

Source: https://postgresapp.com/documentation/remove.html

First determine which PostgreSQL version is installed using the `installed` command. This is a prerequisite for uninstalling the correct server package.

```bash
$ sudo port installed
```

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

### Restore SQL dump with psql

Source: https://postgresapp.com/documentation/migrating-data.html

After stopping the old server and starting the new one, run this command to restore the SQL dump from the compressed file.

```bash
gunzip <postgresapp.sql.gz | psql
```

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

### Check for outdated extensions with pg_available_extensions

Source: https://postgresapp.com/documentation/update.html

Run this SQL query to list installed extensions whose default version differs from the installed version. If results appear, update each extension with ALTER EXTENSION xyz UPDATE; or use postgis_extensions_upgrade() for PostGIS.

```sql
SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL AND default_version <> installed_version;
```

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

### MacPorts: Uninstall PostgreSQL server (example for 9.4)

Source: https://postgresapp.com/documentation/remove.html

Uninstall the PostgreSQL server package. Replace `postgresql94-server` with the version you found in the previous step. After uninstalling, the server may still be running; use Activity Monitor or reboot to stop it.

```bash
$ sudo port uninstall postgresql94-server
```

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

### Enable PL/Python extension with CREATE EXTENSION plpython3u

Source: https://postgresapp.com/documentation/plpython.html

Use this SQL command to enable the PL/Python extension in your PostgreSQL database. Must be run after installing the correct Python version.

```sql
CREATE EXTENSION plpython3u;
```

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

### Remove $PATH config for command line tools

Source: https://postgresapp.com/documentation/install.html

Optional command to remove the $PATH configuration file created during installation, used when uninstalling Postgres.app.

```bash
sudo rm /etc/paths.d/postgresapp
```

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

### Homebrew: Remove PostgreSQL with brew remove postgresql

Source: https://postgresapp.com/documentation/remove.html

Use this command to uninstall PostgreSQL installed via Homebrew. Run it in the terminal before using Postgres.app to avoid port conflicts.

```bash
$ brew remove postgresql
```

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

### Create a database with psql --command

Source: https://postgresapp.com/documentation/migrating-data.html

For each database you want to migrate, run this command to create the database before restoring its data.

```bash
psql --command="create database database_name"
```

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

### List databases with psql --list

Source: https://postgresapp.com/documentation/migrating-data.html

Use this command while the old server is running to show the list of databases you can migrate.

```bash
psql --list
```

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

### Initialize new database cluster with initdb

Source: https://postgresapp.com/documentation/migrating-data.html

In the terminal, create a new database cluster for the new version (e.g., 9.6) with the specified encoding and locale.

```bash
/Applications/Postgres.app/Contents/Versions/9.6/bin/initdb -D ~/Library/Application\ Support/Postgres/var-9.6 --encoding=UTF-8 --locale=en_US.UTF-8
```

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

### Create compressed SQL dump with pg_dumpall

Source: https://postgresapp.com/documentation/migrating-data.html

Run this command while the old server is running to create a compressed SQL dump of the entire server. The output is saved as postgresapp.sql.gz.

```bash
pg_dumpall --quote-all-identifiers | gzip >postgresapp.sql.gz
```

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

### Create missing default database with createdb

Source: https://postgresapp.com/documentation/troubleshooting.html

Command to create the missing default database, fixing the error 'psql: FATAL: database "USERNAME" does not exist'. Requires $PATH configured correctly.

```bash
createdb $USER
```

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

### Dump globals with pg_dumpall --globals-only

Source: https://postgresapp.com/documentation/migrating-data.html

If you have roles and/or tablespaces you need to keep, use this command to create a globals.sql file.

```bash
pg_dumpall --globals-only > globals.sql
```

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

### Manually create default user and database after initialization failure

Source: https://postgresapp.com/documentation/troubleshooting.html

If Postgres.app fails to create the default user or database, use these commands to create them manually. Replace PORT, USERNAME, and DATABASENAME with your values; Postgres.app uses your system user name for USERNAME and DATABASENAME by default. The first command creates a superuser, the second creates the database.

```bash
/Applications/Postgres.app/Contents/Versions/latest/bin/createuser -U postgres -p PORT --superuser USERNAME
```

```bash
/Applications/Postgres.app/Contents/Versions/latest/bin/createdb -U USERNAME -p PORT DATABASENAME
```

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

### Dump a single database with pg_dump

Source: https://postgresapp.com/documentation/migrating-data.html

For each database you want to migrate, run this command to create a dump file named database_name.sql.

```bash
pg_dump database_name > database_name.sql
```

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

### Restore a database with psql -d

Source: https://postgresapp.com/documentation/migrating-data.html

For each database, run this command to restore from the backup file database_name.sql.

```bash
psql -d database_name -f database_name.sql
```

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

### Configure PATH for Postgres.app CLI tools

Source: https://postgresapp.com/documentation/cli-tools.html

Run this command in Terminal to add Postgres.app's bin directory to /etc/paths.d. Requires sudo. Close and reopen Terminal for changes to take effect. Verify with `which psql`.

```bash
sudo mkdir -p /etc/paths.d &&
echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp
```

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

### Manually run initdb to troubleshoot database cluster initialization failure

Source: https://postgresapp.com/documentation/troubleshooting.html

When Postgres.app reports 'Could not initialize database cluster', run this command manually to see the underlying error. Replace 'DATA DIRECTORY' with the actual data directory path. This command initializes a new PostgreSQL database cluster with the specified user, encoding, and locale.

```bash
/Applications/Postgres.app/Contents/Versions/latest/bin/initdb -D "DATA DIRECTORY" -U postgres --encoding=UTF-8 --locale=en_US.UTF-8
```

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

### Connect to a different database with psql

Source: https://postgresapp.com/documentation/troubleshooting.html

Alternative to creating the missing default database: connect to the 'postgres' database instead, fixing the error 'psql: FATAL: database "USERNAME" does not exist'.

```bash
psql postgres
```

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

### Run pg_upgrade from 9.5 to 9.6

Source: https://postgresapp.com/documentation/migrating-data.html

Run this command to upgrade the data directory from version 9.5 to 9.6. It requires the binaries for both versions and the old and new data directories.

```bash
/Applications/Postgres.app/Contents/Versions/9.6/bin/pg_upgrade -b /Applications/Postgres.app/Contents/Versions/9.5/bin -B /Applications/Postgres.app/Contents/Versions/9.6/bin -d ~/Library/Application\ Support/Postgres/var-9.5 -D ~/Library/Application\ Support/Postgres/var-9.6 -v
```

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

### Add Postgres.app bin folder to $PATH

Source: https://postgresapp.com/documentation/install.html

Run this command in Terminal to add Postgres.app's bin folder to your system's $PATH, enabling command line tools like psql. Requires sudo privileges.

```bash
sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp
```

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

### Create missing PostgreSQL user with createuser

Source: https://postgresapp.com/documentation/troubleshooting.html

Command to create a PostgreSQL superuser with the same name as the system user, fixing the error 'psql: FATAL: role "USERNAME" does not exist'. Requires $PATH configured correctly.

```bash
createuser -U postgres -s $USER
```

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

### Default /etc/hosts file on macOS

Source: https://postgresapp.com/documentation/troubleshooting.html

Reference content for the /etc/hosts file on macOS. Used to fix errors like 'Could not create listen socket for localhost' or 'Could not translate host name localhost' caused by a broken hosts file.

```plaintext
##
# Host Database
#
# localhost is used to configure the loopback interface
# when the system is booting.  Do not change this entry.
##
127.0.0.1	localhost
255.255.255.255	broadcasthost
::1             localhost 
```

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

### REINDEX DATABASE command

Source: https://postgresapp.com/documentation/reindex-warning.html

Run this SQL command after connecting to the database to rebuild all indexes in that database. Replace db_name with the actual database name. Repeat for each database if you have multiple.

```sql
REINDEX DATABASE db_name;
```

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

### Delete Postgres.app preferences

Source: https://postgresapp.com/documentation/install.html

Optional command to delete Postgres.app preferences during uninstallation.

```bash
defaults delete com.postgresapp.Postgres2
```

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

### reindexdb --all --port=5432

Source: https://postgresapp.com/documentation/reindex-warning.html

Use this command to reindex all databases on a PostgreSQL server. Requires the path to be configured. Replace 5432 with the port your server is using.

```bash
reindexdb --all --port=5432
```

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

### Display indexes on text-based columns

Source: https://postgresapp.com/documentation/reindex-warning.html

Query to list indexes that contain text-based columns, useful for limiting reindex operations. It joins pg_index with pg_collation to filter collations that are not 'C' or 'POSIX'.

```sql
SELECT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid) 
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s 
JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');
```
Tokens:
Raw