Questions tagged [postgresql-9.1]
PostgreSQL version 9.1
28
questions with no upvoted or accepted answers
17
votes
0
answers
5k
views
PostgreSQL trigram GIST vs. GIN indexes
I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes.
Initially, I started to use GIN ...
- 881
5
votes
1
answer
5k
views
Postgres Streaming Replication: How to re-sync data in master (with standby) after failover
I am absolutely new to postgres 9.1 replication mechanisms.
I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when ...
- 51
3
votes
0
answers
795
views
What happen when Postgresql tablespace is null?
I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s.
Long story short, when I check the disk usage per tablespace, I get different distribution ...
- 141
3
votes
1
answer
2k
views
postgresql multi column unique or check constraint
First a word of pleading. To say I am a newbie to postgresql would be an insult to the newbies! I have been working on this for only a day or so.
I have found several questions on this site, and ...
- 131
2
votes
0
answers
1k
views
Postgresql delete taking lots of CPU and time
I have the following SQL Query for deleting rows from my table. The query takes a long time and puts a high load on my server.
delete from message_log where from_id = ? OR to_id = ?
My table Schema
...
- 213
2
votes
0
answers
863
views
Postgres configuration, incoming connections
Hello I'm trying to figure out the best way to allow connection from certain ips/hosts to my db instance.
I've got an api(s) pointing to the database and right now we do something like this (pg_hba....
2
votes
0
answers
64
views
How to make continues cluster?
I have a report table with the following index:
providerid, date
The table is around 30M records and it grows about 100K rows per day.
I want to use in the index above as a cluster, but as I ...
- 231
2
votes
0
answers
3k
views
"custom archiver out of memory" error when restoring large DB using pg_restore
I'm trying to a restore a local copy of a 30GB DB (with only one table) dumped using the Heroku wrappers (described here) using pg_restore. The Heroku DB is using 9.1.7 and I have had the same result ...
- 335
1
vote
0
answers
258
views
Postgres not able to recover in replication mode
Because of manual cleanup of WAL files, my DB is corrupted. PostGres is throwing this error :
2017-08-10 20:44:41.887 UTC 2661 LOG: database system was interrupted;
last known up at 2017-08-03 ...
- 11
1
vote
0
answers
162
views
Query on postgres 9.5 several times slower than on postgres 9.1
I am running performance tests on two systems that have the same postgres database,
with the same data. One system has postgres 9.1 and the other postgres 9.5.
There are only slight differences in the ...
- 11
1
vote
0
answers
555
views
autovacuum daemon considers pg_largeobject table?
I have a table which is having oid column and few oid column's data might be frequently modified from my application. I thought, it would rewrite the data and replace with new data (basically, thought ...
- 755
1
vote
0
answers
340
views
will vacuumlo find orphaned objects
A Table (mytable) is having 2 columns
id_ | loid_
(integer) | (oid)
Values are
id_ | loid_
1 | 15001
2 | 15002
Value in loid_ column refers the oid from pg_largeobject. And if I ...
- 755
1
vote
0
answers
880
views
Is there any way to reduce pg_largeobject size?
The Backup team are concerned about DB backup since it's huge and taking a long time to back up (physical backup - 300 GB - approx 5hrs). 90% of data is in pg_largeobject.
I could remove loid which ...
- 755
1
vote
0
answers
174
views
pgadmin III backup screen freeze
I wanted to get a backup for a very large database with pgAdmin, I started as usual backups, but after a couple of hours it still runs, and I can't cancel it also.
I don't even get to see the file ...
- 111
1
vote
0
answers
77
views
Error on slave2 after promoting slave1 to master in cascaded streaming replication
I've a setup like MASTER -> SLAVE1 -> SLAVE2. MASTER and SLAVE2 are running on different ports on single server. SLAVE1 in on another server.
For testing purpose I've powered off my first server ...
- 163
1
vote
0
answers
406
views
PostgreSQL query slow when using tsvector and ORDER BY despite having index
Running on a large database (around 1.5G) The following query takes about 70 seconds to run,
SELECT x
FROM DBText x
WHERE x.status = 2
AND x.publishedTime < 4574590406397
AND x.service = '...
- 11
1
vote
0
answers
72
views
Veryfing integrity of custom order column with pure SQL
I have two tables, one stores structure (using so called parent field), the other entities that can be embeded into that structure. As certain level of structure can contain both entities and deeper ...
- 341
1
vote
0
answers
285
views
I am not able to create/reindex a unique index in a table
I have a table that contains user locations (called public.user_location) and is the following:
Column | Type | Modifiers
id | ...
- 21
1
vote
0
answers
2k
views
pg_basebackup could not get WAL end position from server
I'm trying to pg_basebackup my database. I followed instructions in PG Mauals and set it up properly (check on other server and database), but my backup isn't ending - on end of progress it's ...
- 896
1
vote
0
answers
579
views
Storing changes to stored procedure and triggers in PostgreSQL
I want a mechanism where I can store/track changes in stored procedure and triggers.
I want to track these logs in some table. What is the best possible way ?
- 145
1
vote
0
answers
139
views
Postgres SQL Database Recovery
I am recovering a corrupted database, which may not really of had problems, but the server did.
The host has restored a backup of the VM image (Ubuntu 12.04 LTS), however, its 10 days old, which is ...
- 111
1
vote
0
answers
253
views
Pg_restore table size differences
What would account for the difference in table sizes between one PostgreSQL database and another restored from its dump file?
On an Ubuntu 12.04 host, I'm running PostgreSQL 9.1, and I dumped my ...
- 1,294
1
vote
0
answers
143
views
using separate schemas or databases
For my web application with a postgresql backend I'm now looking into schema's. As I switched from MySQL to Postgresql(because of (amongst others) the proper geospatial extension) I was not aware of ...
- 737
1
vote
0
answers
447
views
Postgresql Streaming Replication - pgpool2 - failover
In my scenario I want pgpool to forward read only queries to slaves when the master goes down - I want my app to be in the "read only mode".
How do I setup pgpool to accept read only queries when ...
- 211
0
votes
0
answers
66
views
pg_restore fails with ERROR: could not create directory "pg_tblspc/16400/PG_11_201809051/16403": Invalid argument
Trying to migrate PG9.1 data to PG11.5
I did initdb using the following command.
"<path>\bin\initdb.exe" --locale="English_Malaysia.1252" -D "<path>\pgupgrade"...
- 145
0
votes
0
answers
113
views
Allowing other host to connect to postgresql-9.1 and restart services in postgresql-9.1
We need to add few Ip address in our pg_hba.conf file so that some new servers can connect to our postgresql database. We did the changes to pg_hba.conf like below
old pg_hba.conf
# TYPE DATABASE ...
- 163
0
votes
1
answer
231
views
To get rid of heavy archive logs in PostgreSQL 9.1
In our production server which is having PostgreSQL 9.1 installed, too heavy archive logs are being generated, approximately 41 GB/day. Heavy updates, deletes and inserts are the cause of this. Can ...
- 57
0
votes
1
answer
66
views
Will PostgreSQL server work if I install the service in mounted partition
I have created all tablespaces (only tables created in these tablespaces) in mounted partition, But db is available in pg_default tablespace (root partition where postgresql installed).
I am facing ...
- 755