How to reset database postgresql on Ubuntu 25.10

PostgreSQL is one of the most powerful and reliable open-source relational database systems. It’s widely used for production environments, development, and data analysis. Sometimes, you may need to reset a PostgreSQL database — for example, to remove old data, rebuild the schema, or start with a clean slate for testing.

Update the System

Before starting, make sure your system is up to date:

sudo apt update && sudo apt upgrade -y

Check PostgreSQL Installation

Verify that PostgreSQL is installed and running:

psql --version

If PostgreSQL isn’t installed yet, install it using:

sudo apt install postgresql postgresql-contrib -y

Check the service status:

sudo systemctl status postgresql

If it’s inactive, start it:

sudo systemctl start postgresql

Log in to PostgreSQL

Switch to the PostgreSQL system user:

sudo -i -u postgres

Then open the PostgreSQL interactive shell:

psql

You should now see a prompt like:

postgres=#

Option 1 — Drop and Recreate a Database

This is the fastest and cleanest way to reset a database.

In the PostgreSQL shell, run the following (replace mydb with your database name):

DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;

Exit the shell:

\q

This completely deletes and recreates the database with the same name — ideal for starting fresh.

Option 2 — Delete All Tables (Keep Database)

If you want to keep the database but remove all tables, run these commands inside psql:

DO
$$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END
$$;

This script iterates through all tables and drops them, effectively clearing the database contents while keeping the schema intact.

Option 3 — Restore from a Backup

If you have a backup created earlier (for example, via pg_dump), you can restore it to reset your database to a specific state.

First, drop and recreate your database:

sudo -i -u postgres
psql -c "DROP DATABASE IF EXISTS mydb;"
psql -c "CREATE DATABASE mydb;"

Then restore from your .sql backup:

psql -d mydb -f /path/to/backup.sql

This completely resets your data to the backup version.

Option 4 — Use Command Line Reset Script

You can automate the reset process using a simple shell script.

Create a file:

nano reset_pg.sh

Add this script:

#!/bin/bash
DB_NAME="mydb"
sudo -i -u postgres psql -c "DROP DATABASE IF EXISTS $DB_NAME;"
sudo -i -u postgres psql -c "CREATE DATABASE $DB_NAME;"
echo "PostgreSQL database '$DB_NAME' has been reset successfully."

Make it executable:

chmod +x reset_pg.sh

Then run it:

./reset_pg.sh

This instantly resets your database without manually entering the shell.

Option 5 — Remove All Databases (Except Defaults)

If you want to remove all user-created databases at once, run this inside psql:

DO
$$
DECLARE
    db RECORD;
BEGIN
    FOR db IN SELECT datname FROM pg_database WHERE datistemplate = false AND datname NOT IN ('postgres') LOOP
        EXECUTE 'DROP DATABASE IF EXISTS ' || quote_ident(db.datname);
    END LOOP;
END
$$;

This keeps only the default PostgreSQL databases and deletes everything else.

Restart PostgreSQL Service

After resetting or recreating databases, restart the PostgreSQL service to ensure everything runs smoothly:

sudo systemctl restart postgresql

Resetting a PostgreSQL database on Ubuntu 25.10 can be done in different ways depending on your needs — whether you want to clear data, drop specific tables, or recreate the entire database. These methods help maintain a clean and consistent development or testing environment.

For high-performance Ubuntu VPS servers optimized for PostgreSQL, MySQL, and other databases, visit DropVPS — trusted Linux hosting for developers and businesses.