How to Use PostgreSQL on PythonAnywhere

If you want to use the PostgreSQL database for Python applications on PythonAnywhere, you need to purchase the custom plan.

After purchasing the custom plan, you can set up the PostgreSQL database.

In your PythonAnywhere account, go to the "Database" tab.

First, assign the PostgreSQL Superuser Password.

Also, note the following information in this menu:

• PostgreSQL server version: 12
• Address: username-111.postgres.pythonanywhere-services.com
• Port: 14111
• Superuser role name: super

This information is very important for running the psql and pg_dump commands (for importing and exporting PostgreSQL databases) and for using the correct Django version that supports the current PythonAnywhere PostgreSQL server version.

How to upload SQL  file to PythonAnywhere PostgreSQL database

Step 1: First place the SQL file in your Pythonanywhere user directory (export your SQL file in plain text format).

Step 2: Then use the following command to upload the SQL file to your Pythonanywhere PostgreSQL database

psql  -U your_username -h username-111.postgres.pythonanywhere-services.com -p port_no -d your_database  -f /home/yourusername/your_file.sql

How to download the PostgreSQL database from PythonAnywhere

Open the bash console and type the following command.

pg_dump -h <hostname> -U <username> -d <database_name> -p <port_no> -f  <output_file.sql>

Replace the placeholders with your actual values:

• username-111.postgres.pythonanywhere-services.com: Your PostgreSQL server address.
• your_username: Your PythonAnywhere username.
• your_database: The name of your database.
• backup_file.sql: The name of the output file you want to create (e.g., mydatabase_backup.sql).

Download the Backup File:

• After running the pg_dump command, the backup file (e.g., mydatabase_backup.sql) will be created in your current directory.
• You can download this file using the PythonAnywhere file manager:
  • Go to the Files tab on the PythonAnywhere dashboard.
  • Navigate to the directory where the backup file is located.
  • Click the file to download it.

Note: If you want to create a compressed backup, you can use: (use with port no)

pg_dump -h username-111.postgres.pythonanywhere-services.com -U your_username -d your_database -p 14111 -Fc -f mydatabase_backup.dump

The -Fc option creates a custom-format dump, which can be restored with pg_restore.

psql

psql is the interactive terminal for PostgreSQL. It allows you to execute SQL commands directly against the database.

Common uses:

• Connecting to a PostgreSQL database.
• Executing SQL queries.
• Running scripts.
• Viewing and managing database objects.

Basic usage:

psql -h hostname -U username -d database_name

• -h: Hostname of the PostgreSQL server.
• -U: Username for authentication.
• -d: Name of the database to connect to.

pg_dump

pg_dump is a utility for backing up a PostgreSQL database. It creates a logical backup, meaning it dumps the SQL commands necessary to recreate the database.

Common uses:

• Creating backups of databases.
• Exporting data to be used in other PostgreSQL databases.
• Migrating data between different PostgreSQL installations.

Basic usage:

pg_dump -h hostname -U username -d database_name -f output_file.sql

pg_restore

pg_restore is designed to restore a PostgreSQL database from a backup file. This file can be in various formats, including the custom format, directory format, or tar format created by pg_dump.

Common uses:

• It is commonly used in scenarios where you need to recover data from a backup or migrate data between different PostgreSQL installations.

Basic usage:

pg_restore [OPTIONS]... [DUMPFILE]

Common Options

• -h hostname: Specifies the host of the PostgreSQL server to connect to.
• -U username: Specifies the PostgreSQL user to connect as.
• -d database_name: The target database where the dump will be restored.
• -f output_file: Write the output to a file (usually for custom format).
• -v: Verbose mode; displays detailed information during the restore process.
• -C: Create the database before restoring it. This is useful if you need to create a new database.
• -n schema_name: Restore only the specified schema.
• --data-only: Restore only the data, skipping schema creation.
• --schema-only: Restore only the schema (no data).