All Questions

Tagged with
Filter by
Sorted by
Tagged with
1
vote
1answer
14 views

Can I do a backup without extension functions?

I use the PostGIS extension on my database. The thing is when I do a backup and then a restore, all the PostGIS functions give a warning during the restore process, because the functions already exist,...
1
vote
1answer
27 views

How to take backup of multiple DB in a specific point of time

My test setup is having 3 databases in postgresql-9.6 instance, 2 DBs consumed by 2 live Web apps. I am using pg_dump to take hot backup of all databases. In order to keep CPU load under threshold, ...
1
vote
2answers
31 views

Restoring Postgres from only base backup taken with wal-e

I have a postgres base backup taken via wal-e. Is it possible to restore and start the DB with just the base backup? I cannot use the restore_command in recovery.conf since the wal files are archived ...
0
votes
0answers
13 views

Postgres Database Level Incremental Backups

I need to take database level incremental backup in postgres. I have searched on internet, however all available methods and tools provide whole database cluster level incremental backup. But, not any ...
0
votes
1answer
30 views

Are physical backups sufficient in PostgreSQL? [closed]

Should I make logical backupds too from time to time or are physical backups sufficient for recovery in case of emergency in PostgreSQL? What are the advantages of logical backups? I have heard ...
1
vote
1answer
73 views

postgresql pg_dump out of memory

is it possible to limit somehow usage of resources for pg_dump? The problem is, whole DBase is 775GB and i have two very large tables: pg_largeobject table is – 390GB pg_largeobject statistics: ...
0
votes
2answers
44 views

How to restore a .backup file from a single table on postgres?

I have a .backup dump from a single table, but it didn't work restore like a normal backup from an entire db. I'm creating this backup file with the following batch script bin\pg_dump -h localhost -p ...
0
votes
2answers
84 views

pg_switch_wal In Point In Time Recovery PostgreSQL

I was following this tutorial about point in time recovery in postgres and come up with the following questions. Can anybody help me. Is it necessary to use pg_basebackup every time I want to back up ...
1
vote
1answer
93 views

Postgres backup PITR strategies [closed]

Note:-Might be the question sounds too broad. But, I believe ,it's very good platform(stackexchange) and I can get advice from worldwide expert DBA here. Since, I'm not able to make decision or choose ...
0
votes
2answers
122 views

pg_basebackup failing from another server

I want to take backup of my PostgreSQL database cluster from another server. I'm using below command ,from another server: [root@shiwangini2 pgsql]# sudo -u postgres pg_basebackup -h 192.168.XX.XX -...
0
votes
1answer
13 views

Azure PostgreSQL pg_dump impact on backup “chain”

I'm a SQL Serve DBA new to PostgreSQL. Does pg_dump have an impact on the backup chain, i.e., in SQL Server if I needed a copy of a database I would peform a COPYONLY backup so as not to break the ...
0
votes
1answer
110 views

postgresql recovery.conf file location in 11.5

I'm new to postgres. We need to take incremental backups for postgres databases as well as we must be able to perform PITR. I searched a lot on internet and tried to find out any article referencing ...
0
votes
1answer
48 views

Why Postgres replication write_lag is measured in seconds

I'm running pg_receivewal locally, without compression, it stores segments on the same drive with the db files. Here's the replication statistic: imbolc=# select * from pg_stat_replication; -[ RECORD ...
1
vote
1answer
91 views

How to deal with `.partial` WAL segments generated by Postgres `pg_receivewal` command?

pg_receivewal creates a .partial file for incomplete WAL segment. My question is how to deal with this file during recovery. If I just leave it as it is, last transactions don't recover. If I rename ...
2
votes
2answers
76 views

Is it possible to prevent data loss with Postgres WAL archiving?

As I understand from the docs, a WAL segment is archived when the segment file reaches some size or due to archive_timeout. So if the database crashes, all the data from the incomplete, currently ...
1
vote
2answers
48 views

copy entire PostgreSQL cluster to another (identical) server

I'm looking to clone a PostgreSQL 10 cluster from server1 to server2, which is running the same Postgres version on identical hardware. The purpose is load-balancing and HA. Things to keep in mind: ...
0
votes
0answers
22 views

How to prevent regular users to backup database, in Postgresql

How can I prevent/restrict regular users, that do have select rights granted on tables etc, to perform backup of Postgres database? Is there a way to enable users to connect, select, update etc. ...
1
vote
1answer
123 views

PostgreSQL - do we need to DROP the database and recreate it if we want to RESTORE it?

I worked with many different databases (SQL Server, Oracle, IBM db2) and for all of them if we have a backup it is just enough to execute RESTORE command in order to restore the data. I am beginner ...
0
votes
1answer
24 views

How can I import an old pgdump into an updated schema?

I'm DB newb doing a software upgrade, and my predecessor's original migration plan may not have been as well thought out as I thought... The Plan was to use our backup, created via pg_dump -U derp -...
0
votes
1answer
31 views

PSQL incomplete backup : how to debug

I've been doing backups of my database " screen ". after connecting to PSQL and typing \l+ I'm getting (among other things): Name | Owner | Encoding | Collate | Ctype | ...
0
votes
1answer
133 views

Which PgBackRest policy for differential backups in PostgreSQL

I set up pgBackRest and PostgreSQL configuration in two different servers. I set up with crontab one full backup a week and one differential backup a day. 0 1 * * 0 pgbackrest --type=...
0
votes
1answer
68 views

Barman: What's the best way to switch from backup_method postgres (streaming) to rsync (ssh)?

My production postgresql server is currently backed up with barman using the backup_method postgres (streaming server). I would like to switch to rsync method (ssh) to benefit from better compression. ...
0
votes
1answer
62 views

Monitoring PostgreSQL backup

I am new to PostgreSQL and have questions regarding the backup procedure. How do I check whether a PostgreSQL database was backed up successfully? And how do I monitor the backup? I have tried the ...
0
votes
0answers
13 views

Can pgBackRest be integrated with third-party backup suftware?

I am trying to figure out pgBackRest to backup PostgreSQL databases. So far my understanding is that it can use a local file system or S3 for backup storage. Is there any way to get it to use third-...
0
votes
1answer
224 views

Create publication is not working in PostgreSQL

I am trying to create a publication (for logical replication) in PostgreSQL database but it throws an error: indsolv_dev=# CREATE PUBLICATION my_publication; ERROR: syntax error at or near "...
1
vote
2answers
91 views

How much time should a backup of a 100Gb database take using streaming server setup?

I've installed barman on a dedicated server to backup all my postgresql servers. One of the server is now using 100Gb space and the instruction barman backup myserver takes 23 hours. I find it too ...
0
votes
1answer
37 views

Restoring PostgreSQL tables faster

There's a PostgreSQL PITR system running on a fairly large system, with hundreds of databases with hundreds of tables in each, and we're running into time constraint-based problems when trying to ...
0
votes
1answer
540 views

Barman not able to connect: fe_sendauth: no password supplied

I'm trying to setup streaming replication to a Barman server. Barman config is pretty much basic, I'm using backup_method = postgres. [barman] barman_home = /var/lib/barman barman_user = barman ...
0
votes
1answer
362 views

Migrate complete postgres database cluster to another machine using pg_dump

I am referring to How to get a working and complete PostgreSQL DB backup and test In this example/solution, which part of the restore process does actually the "CREATE DATABASE sandbox"? I have been ...
1
vote
0answers
92 views

Correct config wal-e s3 for minio

Try run PostgreSQL + WAL-E + Minio. Minio: https://github.com/minio/ yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm yum install -...
-1
votes
1answer
90 views

Postgres pg_dumpall failing

Recently on my debian server with postgres 10.6, pg_dumpall has started to fail with the following error: su - postgres -c pg_dumpall | gzip > /temp/directory/postgres_backup.gz pg_dumpall: query ...
0
votes
1answer
69 views

Postgres 9.6 - Taking differenctial backup

I have a few CentOS boxes running Postgres 9.6. I am planning a central server which can make local copies of all DBs on the other servers. These servers are accessible via SSH on Internet. What is ...
0
votes
0answers
212 views

Restore single database (or table-space) using PITR with PostgreSQL?

Is there any possibility restore a single database with WAL files.
1
vote
1answer
31 views

Questions to Continuous Archiving and Point-in-Time Recovery with PostgreSQL

I'm planning my database backup strategy and came across the continuous archiving concept of PostgreSQL which is exactly what I'm looking for. I have two questions on this. I'm planning to mount ...
0
votes
1answer
1k views

Postgresql - Large object exists in pg_largeobject table but cannot open with lo_open?

I have a problem with opening Large Object in postgresql version 9.2, select * from pg_largeobject where loid = '19423'; will return data like this: loid oid;page no integer;data bytea 19423;0;"<...
1
vote
2answers
112 views

How to dump multiple databases of the same cluster in single execution?

I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t. Is there any way to do ...
0
votes
1answer
46 views

verify barman backed up files after failure [closed]

I have a barman that get backups from a postgres installed on another server. its config is: active: True archive_mode: on archived_count: 47588 archiver: True archiver_batch_size:...
2
votes
0answers
580 views

PostgreSQL 10.3 pg_dumpall fails: server closed the connection unexpectedly

System: Windows 10 Pro 64bit, Version 10.0.17134 Build 17134. with Postgres 10.3 db server. I backup my postgres db using the following statements: "C:\PostgreSQL\pg10\bin\pg_dumpall.exe" -U ...
1
vote
0answers
184 views

Handling backups of a large table (>1 TB) in Postgres?

I have a 1TB table (X) that is a pain to backup. The table X contains historical log data that is not often updated after creation. We usually only access a single row at a time, so performance is ...
2
votes
1answer
434 views

How to do per-tenant backups for multi-tenant databases?

Are there any best practices, experiences or guidelines when it comes to do per-tenant backups/restores in a microservice multi-tenant application? In my case I have multiple Postgres servers which ...
0
votes
1answer
260 views

How do I copy a DB dump file to a remote computer? [closed]

I want to create a copy of my database from one server to another (my laptop to a server for example). Ive been looking online about how to do it and found this article:http://www.postgresqltutorial....
1
vote
1answer
18 views

Permission errors on insert after schema drop/create (Pg 9.0.23)

We have a web application that allows users to modify data through an API. In order to allow people to run tests, we provide a sandbox area called 'daily'. This sandbox gets refreshed every day from ...
0
votes
1answer
277 views

Postgresql: How to take incremental backup with Barman

I am new to Postgresql, I've found a tool named Barman for taking incremental backup of database. But the few tutorials aren't helping that much as they're not for naive users. Can someone help ...
0
votes
0answers
168 views

Wal-e : “ pg_stop_backup still waiting for all required WAL segments to be archived”

I've been trying to use wal-e to take incremental backup of my postgresql database by following this blog. I've a database on my postgresql and using an AWS S3 bucket. while I'm using the following ...
1
vote
1answer
606 views

Status of PostgreSQL replication after pause and resume

To backup the PostgreSQL database we setup a slave server which uses streaming replication. We found the commands pg_xlog_replay_pause() and pg_xlog_replay_resume(). So we thought we use the commands ...
2
votes
1answer
1k views

Postgresql: How to take incremental backup with wal-e

I am new to postgresql. I've connected to my demo database with psycopg2 and inserted data with python faker. I want to take incremental backup with the tool WAL-E and want to see how it actually ...
0
votes
2answers
119 views

Test/Verify Log-Shipping Backup

We recently suffered a power/connectivity outage at a data center housing two PostgreSQL 8.4 secondary servers, that remotely back up our two primaries via log-shipping replication. Backup power kept ...
0
votes
1answer
51 views

Correct way to re-use or recover databases from “PostgreSQL 9.3” to “PostgreSQL 9.5”

I was using PostgreSQL 9.3, then I installed PostgreSQL 9.5, then I unistalled PostgreSQL 9.3. Now I want to move the database from the folder of PostgreSQL/9.3/data/base of PostgresSQL 9.3 to ...
-1
votes
1answer
218 views

Archiving strategy

I have an instance with only one database which contains 4 years of data (400 gigas). I name this instance inst_live I want to implement this following archiving strategy: create an archive instance (...
0
votes
1answer
34 views

Why provide database name when starting postgresql base backup

According to the postgresql 8 manual, before starting a base backup (i.e. copying the files out of the data directory), you need to issue a command like this: psql -d DATABASENAME -c "SELECT ...