Backup database (Linux)
|
1 |
pg_dump -U postgres -W --host localhost --port 5432 your_database | gzip > /opt/backup/your_database-$(date +%d-%m-%Y_%H-%M-%S).gz |
Backup database (Windows – Method 1)
|
1 |
pg_dump -U postgres -W --host localhost --port 5432 your_database > d:/your_database.sql |
Backup database (Windows – Method 2)
|
1 |
pg_dump -U postgres --host localhost --port 5432 --no-owner --no-privileges your_database > d:/your_database.sql |
Restore database (Linux)
|
1 |
zcat /home/odenktools/your_database_2021-12-13T00:08:36Z.sql.gz | psql -U postgres -p 5432 -W your_database_name |
Restore database (Linux / Windows)
|
1 |
psql -U postgres -W -d your_database -h localhost -p 5432 -f d:/your_database.sql |
Change default postgres password (Linux)
|
1 2 |
sudo -u postgres psql \password postgres |
Create New User (Linux – Method 1)
|
1 2 |
su - postgres createuser your_user -s --pwprompt |
Create New User (Linux – Method 2)
|
1 2 3 4 |
su postgres psql -U postgres -d postgres -h localhost -p 5432 CREATE USER your_user WITH PASSWORD 'your_password'; ALTER ROLE your_user SUPERUSER; |
Create new Database (Linux – Method 1)
|
1 2 3 |
su - postgres createdb your_new_database psql -s your_new_database |
Create new Database (Linux – Method 2)
|
1 2 3 |
su postgres psql -U postgres -h localhost -p 5432 CREATE DATABASE your_database WITH OWNER postgres ENCODING UTF8; |
Connect to database
|
1 2 |
su postgres psql -U postgres -d your_database -h localhost -W |
Refresh MaterializeView
|
1 2 3 |
su postgres psql -U postgres -d your_database -h localhost -W REFRESH MATERIALIZED VIEW public.v_location_address WITH DATA; |