33 questions
- Bountied 0
- Unanswered
- Frequent
- Score
- Trending
- Week
- Month
- Unanswered (my tags)
-1
votes
0
answers
30
views
How to upgrade cluster from Postgres 17 Windows to Postgres 18 Debian [migrated]
Postgres 17 cluster is in Windows server in Estonian locale. Databases are defined like
CREATE DATABASE mydb
WITH
OWNER = mydb_owner
ENCODING = 'UTF8'
LC_COLLATE = 'et-EE'
LC_CTYPE ...
1
vote
1
answer
91
views
Why is my custom function inside pg_catalog not included in pg_dump or pg_upgrade?
I created a custom SQL function inside the pg_catalog schema in PostgreSQL. However, when I run pg_dump or perform a pg_upgrade, the function doesn't get included in the dump or upgraded cluster.
Here’...
0
votes
1
answer
162
views
Omit roles creation from pg_dumpall
How can I omit in output from pg_dumpall this roles creation part:
--
-- Roles
--
CREATE ROLE admin;
ALTER ROLE admin WITH NOSUPERUSER INHERIT NOCREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS ...
0
votes
0
answers
98
views
Why I get errors for `REFRESH MATERIALIZED VIEW` during `pg_dumpall | psql`?
I am migrating from PostgreSQL v15 to v16. During migration I got this error:
GRANT
ERROR: function unaccent(text) does not exist
LINE 2: SELECT unaccent(coalesce(1,ドル ''));
^
HINT: ...
0
votes
1
answer
679
views
Need some clarification on dropping database during pg_restore
When using pg_dump with the clean option, it generates SQL statements tailored to drop objects when restoring with pg_restore (pg_dump custom format file contains 'DROP DATABASE'). If pg_dump ...
1
vote
2
answers
118
views
Is there a way to add the password at a parameter and get it automatically when needed in a batch file?
I have the following batch file in windows, in order to backup postgresql daily.
I will add this batch file to run daily at specific time in Task Scheduler.
pg_dumpall --exclude-database='template1' --...
0
votes
0
answers
58
views
postgres backup restoration failing
I am trying to restore backup taken by pg_dumpall (filesize 16 GB) command:
psql -f backup_filename
but it is showing me out of memory error and process stops.
During restore when I see duplicate ...
0
votes
1
answer
323
views
Necessary Camunda db tables for application to run
I have a bash script that dumps globals and all schemas in cluster but dumps data only from 1 schema then imports the dump files in a new database inside a docker container.
Anything regarding Camunda ...
0
votes
0
answers
562
views
Export a postgresql database
I want to export a postgresql database named "kd" with all roles, tablespaces, etc. I run the command pg_dumpall -U sce -h localhost -p 5450 -d kd > /tmp/db.sql I get the error
...
1
vote
0
answers
32
views
Duplicating an entire RDS instance PostgreSQL DB to another DB within the same instance [duplicate]
I'm trying to create a new DB (as a perfect copy of an existing RDS Instance DB, including data) when developers open a pull request. Since it's temporary, I need it to be created as fast as possible, ...
4
votes
1
answer
5k
views
Custom format (-Fc) when backing up postgres with pd_dumpall instead of pg_dump
pg_dump has a great option, it allows you to select custom format for the output of the backup.
Output a custom-format archive suitable for input into pg_restore. Together with the directory output ...
0
votes
1
answer
304
views
how to decompress .sql extension file in windows server
I have taken full backup of postgresql database which consists of 100 databases. The backup format is .sql (eg pg_dumpall.exe -U postgres > D:\Backup\fullbkp.sql) now one of my database got crashed ...
3
votes
1
answer
1k
views
How can I run pg_dumpall with Heroku?
How can I use pg_dumpall with Heroku? The default "database backup" feature from Heroku is pg_dump with the click of a button, which doesn't include roles, so I want to do pg_dumpall ... I'm ...
0
votes
0
answers
127
views
Migrating db from pgsql 9.5 to 12.4 via pg_dumpall does not include all tables
I am trying to migrate three databases to a new server (Linux centos 8 - pgsql 12.4)
So I am using "pg_dumpall" and target the old server - backups are getting created, then I am using psql ...
0
votes
1
answer
670
views
AWS RDS Postgres error while taking the dump
When I try to take PostgresDump (AWS RDS) the following error I am getting:
ERROR: permission denied for relation dms_stats_detailed
pg_dump: error: query was: LOCK TABLE table_name IN ACCESS SHARE ...
user avatar
user8445210