Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
1
vote
2answers
1k views

Optimizing query with type casting

I had a query that was taking way too much time despite having all the indexes I could think of. Eventually, I realized that one of the JOIN .. ON inside the query was casting a column's content to ...
1
vote
1answer
37 views

Does cancelling (stopping early) a CLUSTER command cause a full rollback in Postgres 9.5?

Let's say I want to CLUSTER a table. There's no way to know how long it will take so I just run it. After a couple hours, I need the data so I cancel the CLUSTER command. Does CLUSTERing work with ...
1
vote
2answers
1k views

extension “pg_buffercache” already exists and relation “pg_buffercache” does not exist

When I try to create the extension pg_buffercahe using the following command: ➤ psql://postgres@postgresql-95-2:5432/postgres CREATE EXTENSION pg_buffercache; ...it returns the error: ERROR: ...
1
vote
1answer
613 views

How to modify a Postgres tablespace location

I am using a workstation that has 3 different hard drives. Some time ago I got a large database to reverse engineer that would not fit in the system drive. Therefore I created a new table-space in one ...
1
vote
1answer
169 views

Count number of writes to different tables

I have recently setup Continuous Archiving for Point-in-Time Recovery in a postgresql DB, but the archive is growing at an unexpectedly high pace (tens of gigabytes per day). The DB is a couple of GB, ...
1
vote
1answer
84 views

Ordering in postgresql

I'm facing an issue whereas I do not understand. Running PostgreSQL on mac and have this query: select tldid, tldaction, to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, ...
1
vote
1answer
49 views

Query performance depends on parameter value

In the system I'm working with there are many type hierarchies, where each type corresponds to a table. For example f_dp_rkkbase -> f_dp_rkk -> f_dp_inputrkk. To distinguish between different ...
1
vote
1answer
45 views

Update “oldest” row

I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n). I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
1
vote
1answer
81 views

How to improve performance of query for distinct suppliers in big sales table?

I have two tables, one with store data: Table "public.store" Column | Type | Modifiers -----------+------------------------+----------- id | integer ...
1
vote
2answers
73 views

How do I return two aliases from the same table query?

How would I do something like this: SELECT * from notifications WHERE read=false AS unread_notifications and WHERE read=true AND deleted=false AS read_notifications; Result Example: ...
1
vote
1answer
105 views

how to execute sql inside a column in postgres

I am trying to execute SQL statements that exist within a column of a database table. The records of this table has 'tests' in each row. sid name sql 1 test1 SELECT source_system_name, ...
1
vote
1answer
3k views

How to select unique records in PostgreSQL without using Distinct clause [closed]

I have a query which selects number of records for each house. SELECT -- 3 rows a.house, -- 12 rows (for each house) a.id, -- for each ID (there are 4 repeating records) -- out of ...
1
vote
2answers
122 views

Looking for ways to optimize sql query with inner join

My join query seems to be using the right index but is still slow. I am not sure where the issue lies and would appreciate any tips. The query and its explain is as follows: SELECT events.*, ...
1
vote
1answer
2k views

Failed import of date using COPY

I'm trying to import a CSV file into a table in Postgres, and it's bombing out on the first date field. The dates are in the format dd/mm/yyyy and there isn't a date in the field in question in the ...
1
vote
1answer
2k views

Select rows from my table where a character column matches a pattern

I'm using Postgres 9.5. I have a name column of type "character varying". I wonder how to select rows where the column begins with a number, followed by a space, followed by any other data. So I ...
1
vote
2answers
128 views

Working of postgres remote connection

How does psql remote connection work (default configuration)? Does it make use of ssh or any other protocol? I am using linux machine (centos 6.8) and postgresql9.5 version.
1
vote
1answer
959 views

Limit of psql_history

I can't find (using ctrl-r within psql and less ~/.psql_history) a particular query that I executed a couple months back. I also recently upgraded from 9.3 to 9.5 so I'm not sure if there is a limit ...
1
vote
2answers
762 views

PITR restore in postgresql

I need to perform incremental backups and restore those backups in postgresql. So in order to achieve this i setup wal-archiving as follows. wal_level = archive archive_mode = on archive_command = ...
1
vote
2answers
9k views

Start, stop, configure PostgreSQL through Mac terminal

I am having basic problems using PostgreSQL on my Mac OSX Yosemite, through the terminal. I tried google-ing but this is becoming increasingly difficult and info very scattered so I keep running into ...
1
vote
1answer
684 views

Postgres sharding installation with citusdb

We are developing a prototype for a BIG data product. We have almost 2 billion records. We have used PostgresSQL 9.5 as a back-end and Python as front-end. We are using a 16*2.4 GHz processors with ...
1
vote
1answer
57 views

Should I stop my PostgreSQL server before reboot?

Every week I do an apt-get upgrade and dist-upgrade on the server where my PostgreSQL is running. After that, I do a sudo reboot now. Should I stop the PostgreSQL service before this reboot?
1
vote
1answer
25 views

Can sub-selects change in one single query in a read committed transaction?

I am querying the same table multiple times in a single query in a transaction with isolation level set to READ COMMITTED (which is the default in PostgreSQL). Examples are: BEGIN; WITH first AS (...
1
vote
1answer
46 views

Postgresql 9.5 JSON problems

I have to update a complex query that takes JSON fields: SELECT grower, fieldname as "Field/Block/Block Name", downloaddate, area, LandType from SELECT context->>'Source' as Product, ...
1
vote
1answer
305 views

Postgres not using expression index

I am using Postgres 9.5. It is possible this is related to the changes in 9.6 pointed out by PostgreSQL not using index during count(*), but I cannot be sure yet. I have a table that has a character ...
1
vote
2answers
226 views

PostgreSQL: why does an extra WHERE clause make an index-only scan slow?

I have a query that is being made 18x slower by the presence of a single WHERE clause. The query plan is an index-only scan and the clause in question looks at one of the fields in the index. This ...
1
vote
1answer
1k views

Postgresql-Server won't start after moving data directory

I am running a local postgresql server (9.5 & 9.6) on my Ubuntu 17.04 machine. After moving my data directories to another hard drive following this guide the server can't start. I.e. with sudo ...
1
vote
2answers
85 views

PGSQL: Which is faster, many constraints or a single trigger?

I have a table that currently has 12 check constraints. I'm making some updates and I'm wondering if this is the best approach. I see three options: Keep using the 12 constraints. They're a bit ...
1
vote
1answer
90 views

Index for range of timestamp with varchar fields

I am using postgress database (Version 9.5.4), I have a table structure like this, create table test ( id serial primary key, company_name varchar(255) not null, department varchar(255) ...
1
vote
1answer
340 views

SELECT query slowing down the wal replay in replication server

While running a SELECT query (bit large query ~1GB of data output which took ~15minutes to execute) in hot standby replication host, its halts the wal archiving process. Once the Query done with the ...
1
vote
2answers
120 views

How to calculate column (integer) changes in hourly buckets? [closed]

I have following table: id serial pk created_at timestamp with time zone not null user_id integer not null points integer not null For a given time period (min_date and max_date, both timestamp with ...
1
vote
1answer
1k views

how can i lock or disable connections to postgres DB while performing maintenance? [duplicate]

I never have this issue with MySQL but PostgreSQL is ALWAYS a pain when trying to do any kind of maintenance. So I am trying to truncate and load new data into databases on PostgreSQL server but ...
1
vote
1answer
369 views

Efficient pagination for big amount of data [closed]

I need to be able to quickly (1-2 seconds) retrieve pages of 50,000 records from table that contains ~3,000,000 records. There's a UNIQUE index on a string primary key which contains values like '...
1
vote
1answer
292 views

how to convert my PostgreSQL database table into CSV? [closed]

i need to convert my PostgreSQL table into csv, how to do this? i have visited many sites they suggest the PostgreSQL command to convert the table into CSV but i always get the error message. the ...
1
vote
1answer
2k views

Conditional WHERE in plpgsql function

I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not ...
1
vote
1answer
47 views

Is there a way to connect a Postgres 9 database client to a Postgres 8 system?

I a now work mainly with Postgres 9.5, but I alsoo need to connect to a number Postgres 8 databases connect them probably as different schemas, if that is possible. Are foreign database wrappers the ...
1
vote
2answers
622 views

Issues with setting a global setting in PostgreSQL 9.5/PostGIS2.2

I am trying to set a global setting/GUC variable postgis.gdal_enabled_drivers from the PostGIS extension for a while. I am a non-admin user but can ask admin to change settings if needed. But I tried ...
1
vote
1answer
1k views

PostgreSQL streaming replication lag for synchronous standbys

We use PostgreSQL 9.5 in a master + synchronous hot-standby setup, i.e., using synchronous_commit = on and using synchronous_standby_names='*'. Consequently, the standby has sync_state = sync in the ...
1
vote
1answer
164 views

max_stack_depth not changed even stack changed in limits.conf

The attempt of making changes to max_stack_depth using select set_config('max_stack_depth', '512MB', false); because of the error shown in postgresql log: DETAIL: "max_stack_depth" must not exceed ...
1
vote
1answer
151 views

Truncation vs Deletion When `session_replication_role = replica`

When I first run SET session_replication_role = DEFAULT; CREATE TABLE users ( id serial PRIMARY KEY ); CREATE TABLE posts( id serial PRIMARY KEY, user_id INTEGER REFERENCES users(id) ); ...
1
vote
0answers
15 views

After a PC freeze (Windows 7) and disk integrity check, most data from PostgreSQL table was deleted

My development server is on a PC, using 64-bit Windows 7. My PC has been randomly freezing on me lately, requiring a hard reboot (holding down power button until PC turns off) Yesterday, as I was ...
1
vote
0answers
395 views

ERROR: invalid input syntax for type oid: “english”

While creating an index in Postgres I am getting an error: invalid input syntax for type oid: "english" LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',... The query ...
1
vote
0answers
20 views

Query that fetchs average values over timestamp range of time, on a certain value of another pair of fields

This is the situation, over the time our table reached around 800 millions rows. And it is going to get bigger and bigger. It is a service that collects metrics of energy (voltage, power, current, etc)...
1
vote
1answer
294 views

WAL files not being deleted and pg_drop_replication_slot('barman') causes [55000] ERROR: replication slots can only be used if wal_level > = archive

My database has stopped deleting WAL files and pg_xlog grows to eventually take all my disk space. I have to use pg_controldata and pg_archivecleanup to delete them manually. I'm almost certain it'...
1
vote
0answers
19 views

Disconnect specific database from pgpool II

I'm using PostgreSQL 9.5 with pgpool II version 3.4.13 on Ubuntu 16.04.3 I want to drop a specific database so I must disconnect the database from pgpool. How can I do this without disconnecting the ...
1
vote
1answer
730 views

Parse error in pg_hba.conf [closed]

Hope this is not as stupid a question as I think it is. But I'm in a loop and unable to find a solution. I installed Postgres in centos machine. The installation worked fine (apparently). Now I have ...
1
vote
2answers
116 views

How to recover data from a corrupted /base/ directory? [closed]

I have deleted all directory by mistake except from the given link /var/lib/postgresql/9.5/main/base/18686 and so on. I have 4 such directories, now I want to get data back from those folder as they ...
1
vote
1answer
112 views

How to synchronize a Windows database for Linux?

I have some database (PostgreSQL 9.5) on a Windows server, and I would like this data to be synchronized to a Linux server, that is connected in an application. Is it possible to synchronize?
1
vote
0answers
37 views

Impact of numerous indexes on query optimizer for a delete statement

We have a 10GB table with 30GB indexes on it. The table consists of a few dozens of attributes a primary key on the id (varchar(32) type) and a dozen of indexes on anything but id key (except the ...
1
vote
1answer
476 views

Access denied error when archiving WAL during postgresql 9.5 replication setup

I want to setup Postgres replication on Windows. I followed the given youtube url step by step: youtube video. For archive_command I use this: archive_command = 'copy "%p" "\\\\ip-address of ...
1
vote
1answer
219 views

Exclusion constraint on ENUM errors: data type dow has no default operator class for access method “gist”

Following in the style of this, I'm trying to create an exclusion constraint over an ENUM, CREATE TYPE dow AS ENUM ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' );...