Skip to main content

Questions tagged [postgresql-9.3]

PostgreSQL version 9.3

76 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4 votes
0 answers
194 views

PostgreSQL/PostGIS: Query seems inordinately time-consuming

I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other). Both tables have GIST indices on their spatial ...
GT.'s user avatar
  • 215
4 votes
0 answers
4k views

Postgres Won't Start - incomplete startup packet

I have an issue that is preventing a Postgres server from starting. The server fell over after it ran out of disk space, how ever this has now been resolved although now Postgres will not start. It ...
Cheyne's user avatar
  • 153
3 votes
0 answers
2k views

There seems to be a postmaster servicing the old cluster. | Upgrade from postgres 9.3

I am working on upgrading postgres from version 9.3 to 10. I have encountered a lot of issues along the way since the install is way more custom done than it should be (People here LOVE complex and ...
Josh Kirby's user avatar
3 votes
0 answers
1k views

Postgres production server: more rollbacks than commits?

I'm studying a little Postgres 9.3 stats, and I've observed that in my servers, this simple query: select sum(xact_commit) as commits,sum(xact_rollback) as rollbacks from pg_stat_database; I have ...
david.perez's user avatar
3 votes
0 answers
369 views

High tup_returned value for a dormant database

I was reviewing some statistics on a set of production databases on a Postgresql-9.3 server by running this query: SELECT * FROM pg_stat_database ORDER BY tup_returned DESC; I noticed some ...
Elliot B.'s user avatar
  • 131
3 votes
0 answers
1k views

SELECT query blocking writes in postgresql

We have a web application that sends emails with a queue worker. Before an email is sent we write to a table: INSERT INTO user_email (user_id, email_key, sent_at) VALUES ( 175413579283955991, ...
Michael Mattax's user avatar
3 votes
0 answers
44 views

Upgrade PostgreSQL 9.3 without moving the DB and messing PostGIS on OSX OpenGeo Suite original installation

I looked for the answer, but could not find a specifically fitting one, so would appreciate an answer or a pointer to one... Q: How do I upgrade PostgreSQL 9.3 to the current (now 9.6 ?) without ...
Lizardie's user avatar
  • 773
3 votes
0 answers
2k views

SELECT blocking SELECT?

I have huge outputs from pg_stat_activity and the result from the second "combination of blocked and blocking activity" report from https://wiki.postgresql.org/wiki/Lock_Monitoring (2nd version), with ...
Ivan Voras's user avatar
3 votes
0 answers
520 views

Postgresql upgrade issue

I'm trying to test the Postgresql 9.3 -> 9.4 upgrade process on the development environment on windows and I get weird issue during the pg_upgrade invocation: PS D:\bin\postgres-9.4> .\bin\...
jezzarax's user avatar
  • 181
3 votes
0 answers
766 views

Unique Index much bigger than the table itself

Pg 9.3 on debian 7. I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small ...
Ivan De Sousa Paz's user avatar
3 votes
0 answers
1k views

Optimizing a single table, write-once, read-many PostgreSQL database

I've just finished constructing a table of ~835 million rows using Google's ngram dataset, aggregated on the years in which they occurred so that each 2-,3-,4-, and 5-gram is represented by a single ...
James Pizzurro's user avatar
2 votes
0 answers
59 views

the cost decreased but the time was practically the same, why?

Table definition: CREATE TABLE schema.mylogoperation ( id_mylogoperation serial, data DATE, myschema VARCHAR(255), column_var_2 VARCHAR(255), user VARCHAR(255), action TEXT, ...
Gabriel's user avatar
  • 153
2 votes
0 answers
710 views

Postgresql Long Startup time during queries

I have run two queries on the two different RDS boxes (one is the snapshot of the other). However, they have wildly different execution times: Aggregate (cost=444569.62..444569.63 rows=1 width=0) (...
DG428's user avatar
  • 21
2 votes
0 answers
767 views

Amount by which to increase checkpoint_segments (postgresql)

In my postgresql log, I see statements such as checkpoints are occurring too frequently (26 seconds apart). Currently in the conf, checkpoint_segments are 32, checkpoint_timeout is 15 mins and ...
Hassan Baig's user avatar
  • 1,979
2 votes
0 answers
818 views

Import Data From csv Into Postgresql WITHOUT COPY

I am writing a function for a python environment which needs to import data from a csv file into an existing table. The scope in which the function can execute does not allow for a db SUPERUSER to run ...
Phillip Stack's user avatar
2 votes
0 answers
84 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
nzifnab's user avatar
  • 121
2 votes
0 answers
678 views

Manual postgres migration for Django (python) app

I have a Django (python) data model called Message: class Message(models.Model): text = models.CharField(max_length=50) sending_time = models.DateTimeField(auto_now_add=True) I added sender =...
Hassan Baig's user avatar
  • 1,979
2 votes
0 answers
462 views

PostgreSQL 9.3 running on Linux - need to authenticate vs. Windows Active Directory

Database server is Ubuntu Linux 3.13.0-68-generic Database is PostgreSQL 9.3.10 LDAP set up according to https://cdc.iseage.org/tutorial-pam-ldap-authentication-active-directory-debianubuntu/ pg_hba....
Zeki's user avatar
  • 21
2 votes
0 answers
446 views

Postgresql: Limits of tsquery field

I'm using a tsquery field in order to pre-compute batch searches, but I'm encountering this error while updating a tsquery field on a table: ERROR: row is too big: size 10704, maximum size 8160 The ...
paul.ago's user avatar
  • 121
2 votes
1 answer
4k views

Postgresql: Partition table by hour

I have a system that inserts several million records over the course of 24 hours. Users don't care about the data that's older than 24 hours, and typically query data over a small time frame (5 to 60 ...
CurtisHx's user avatar
  • 123
2 votes
0 answers
678 views

postgresql 9.3 with Ubuntu 14.04.1 LTS throw 's ERROR: invalid page in block 105 of relation base/16385/17407

This is Ubuntu 14.04.1 LTS with postgresql 9.3 in production, Now i got this below error's in log. # tail -f /var/log/postgresql/postgresql-9.3-main.log 2015-01-08 14:10:16 IST CONTEXT: automatic ...
Babin Lonston's user avatar
2 votes
0 answers
437 views

Postgresql service run failure

Trying to setup replication through the wal shipping using Postgresql 9.3 x64. Used this instruction from youtube. The problem is - the slave postgresql service won't run. This is what in logs ...
Danil Gholtsman's user avatar
2 votes
0 answers
782 views

PostgreSQL service frozen and can't restart properly

I have Windows Server 2008 R2 and PostgreSQL 9.3. Server is still for testing so I'm alone and only have a .net application doing consult with npgsql to the db, mostly doing some stored procedures and ...
Juan Carlos Oropeza's user avatar
1 vote
0 answers
6k views

How to run multiple sql statements in Postgres using the tool PGADMIN?

I have 170 Alter Drop Constraint statement, 120 Truncate table, 120 Insert table and 170 Alter Add Constraint. All of these should be run in one in one script as batch script and I'm using PGADMIN IV ...
Ganesh Saragadam's user avatar
1 vote
0 answers
72 views

EDB Replication Server - Replicate from EDB Postgres Advanced Server 9.3 to EDB Postgres Advanced Server 9.6

I am new to EDB Replication Server and I was wondering if I can use this tool to upgrade from EDB Postgres Plus Advanced Server 9.3 to EDB Postgres Plus Advanced Server 9.6. Is this a bad idea? What ...
luckypost's user avatar
1 vote
0 answers
97 views

PostgreSQL is not working after installing Postgres-XC

I am using PostgreSQL 9.3 on Ubuntu 17. This set up was working fine, but it stopped working after installing Postgres-XC. I tried uninstalling Postgres-XC and reverting back the data directory to ...
tapas chakrabarty's user avatar
1 vote
0 answers
2k views

PostgreSQL query very slow

I've got some issues with a Postgres database being very slow. To test/troubleshoot I installed Postgres on various machines: Ubuntu 14.05 32bit on ESXi, postgres 9.3 32bit Windows 7 Professional 64 ...
RobbeM's user avatar
  • 111
1 vote
0 answers
1k views

PostgreSQL - REFRESH MATERIALIZED VIEW while pg_dump

(Version 9.3) Asking this after searching around without any solution: I create a simple materialized view (in practice, I have a few of them). I call pg_dump to backup the database. While pg_dump ...
gilad905's user avatar
  • 121
1 vote
0 answers
144 views

postgres streaming replication slave ignores zero_damaged_pages

I have: 2017-04-19 09:33:26 UTC :: WARNING: page 43087 of relation base/11137505/11139144 is uninitialized 2017-04-19 09:33:26 UTC :: CONTEXT: xlog redo visible: rel 1663/11137505/11139144; blk ...
Vao Tsun's user avatar
  • 1,213
1 vote
0 answers
662 views

Avoid changing table reference in view after renaming the main table

My table name 'Transaction'. I renamed the table name as 'Transaction_backup' and I have created a new Table 'Transaction'. When I renamed the old table, the table reference in view also changed ...
Vidhya's user avatar
  • 11
1 vote
0 answers
1k views

How check server resources in postgresql

In my local pc/db, I have a C# app calling Postgres user functions using multiple threads (BackgroundWorkers) using NpgSQL. But after 1h hour something happen and process stop. My guess is something ...
Juan Carlos Oropeza's user avatar
1 vote
0 answers
201 views

SET NULL fails with consecutive cascade operations

I have tables for country/province/district/commune/village. Each have a foreign key on its parent with a cascade delete. I then have a location table with foreign keys to each tables above with a ...
Jerome B's user avatar
1 vote
0 answers
2k views

Check Postgres Listener is up/down?

I am using PostgreSQL LISTEN/NOTIFY functionality in Java. So I have made a ListenerBean which is run at Application startup. LISTENER class in java: @Singleton @Startup ...
Siddharth Trikha's user avatar
1 vote
0 answers
870 views

Force Postgres to write log to a new file

I've updated the value of log_line_prefix, and I wanted that the log file will contain only logs with the new prefix. I deleted the log file (postgresql-9.3-main.log), assuming that once the file is ...
lev's user avatar
  • 111
1 vote
0 answers
118 views

How can I upgrade PostgreSQL while using PgQ queue system?

I'm using PostgreSQL 9.3 and PgQ 3.2.6 for about two years and now it's time to upgrade. I found out that upgrading PostgreSQL is fairy simple. Just install new Postgres version and then: sudo ...
Ivan Velichko's user avatar
1 vote
0 answers
64 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
Jack Talbot's user avatar
1 vote
0 answers
65 views

My simple FK constraints appear to have been ignored. How?

I have two tables, device and platform where device.platform_id enforces the following constraint: Foreign-key constraints: "device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES platform(...
msanford's user avatar
  • 164
1 vote
0 answers
978 views

Foreign key violations detected upon UPDATE on another column

I have a table country that is referenced by many tables like so: db=# \d client Table "public.client" Column | Type | Modifiers --...
pyb's user avatar
  • 231
1 vote
1 answer
45 views

Is log shipping good for periodic synchronization?

I have Postgresql 9.3 server on both my laptop and my desktop at home. I keep identical data on both system. Sometimes I work on the database on my laptop while other times I work on the database on ...
krishnab's user avatar
  • 181
1 vote
0 answers
145 views

xmlforest() omits any fields that are null entirely, instead of creating an "empty" element

I'm know I can use an empty string, but I don't want to lose the null functionality in the database. In some cases null is appropriate and I can't use empty strings. Other than a empty string, how ...
E.E's user avatar
  • 111
1 vote
0 answers
47 views

Logging within a procedure's transaction

I have an ETL process running inside a PostgreSQL 9.3 procedure on AWS RDS. The procedure normally takes about ten minutes to run, but I'm having a rare issue where it takes several hours and never ...
John's user avatar
  • 757
1 vote
0 answers
200 views

Postgresql FDW oracle out of memory

I have a linux box with centos 6.7 and postgres 9.3.9. I have a foreign data wrapper throught an oracle 11.2 box and a jdbc driver. It's working fine since several months, but yesterday I had to ...
mélanie's user avatar
1 vote
0 answers
199 views

Query planner slow to use newly created index on database under heavy load

We have some heavily loaded databases with more than 1k requests / sec and have started to have problem with index maintenance. Sometimes we need to update/recreate one of the indexes we have, for ...
viblo's user avatar
  • 479
1 vote
1 answer
135 views

PostgreSQL full-text search retrival from id gap

I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part). There is a postgres function where I SELECT concat_ws(' ', a."...
gevra's user avatar
  • 119
1 vote
0 answers
531 views

PostgreSQL 9.3: Minimize locking when updating a large table with primary key

I am building a DB solution to handle a fast stream of online events. Each event UPSERTs a row in a location_update table, using its car_id primary key. Since there are very few new car_ids, the ...
Adam Matan's user avatar
  • 11.7k
1 vote
0 answers
241 views

What is the best way to clear couple of tables each night at specific hour in Postgres SQL?

I need to clear couple of tables every night on my db so in the morning they will be empty and ready to use for the end users. I already wrote the query to truncate them but I'm not sure what is the ...
Alophind's user avatar
  • 121
1 vote
0 answers
1k views

Master/Slave - streaming replication restrict function to master

How can I run a function a on master only? I don't want to connect directly to my master server, because the master server might change. Or can I change my function to work on this setup? Because ...
alexsmn's user avatar
  • 353
1 vote
0 answers
210 views

How to speed up query which is using <@> operator?

I am using earthdistance extension (along with cube extension). I have following query: SELECT "User"."id" ,"User"."displayName" ,"User"."firstName" ,"User"."lastName" ,"User"."email"...
user606521's user avatar
  • 1,375
1 vote
0 answers
374 views

Possible to get log file with only statements sent to Postgres from my app (and not from psql prompt)?

I'm using an ORM in my app and would like to see all statements sent to Postgres so that I can figure out exactly what the ORM is doing. In the Postgres config file I added log_statement = 'all' to ...
user779159's user avatar
1 vote
0 answers
7k views

ERROR: relation "name" does not exist

I am trying to import a postgresql database table sent to me in phpPgAdmin, I am following the instruction from this import database But after doing so, several error appear, I also tried to import ...
Jin's user avatar
  • 3,843