Questions tagged [postgresql-9.4]

PostgreSQL version 9.4

113 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
504 views

Inner join on array columns from 3 tables

I have 2 tables that are related between them using integer data which is stored in arrays. Data table telemetria_data.historico_354677050164513 CREATE TABLE historico_354677050164513 ( imei ...
1
vote
0answers
2k views

Postgres 9.4 to 9.5 using Yum and pg_upgrade - CentOS 6.7 (Final)

I am attempting to do an upgrade between Postgres 9.4 to 9.5 on a CentOS 6.7 system. I have reviewed both: http://www.postgresonline.com/journal/archives/362-An-almost-idiots-guide-to-install-...
1
vote
0answers
606 views

Postgresql 9.4 replication not working

I have setup my replication as per https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps. When I start my slave and check for ...
1
vote
0answers
266 views

PostgreSQL Multi Master Single Slave Replication

I have multiple transaction PostgresQL 9.4 databases and a single publication read-only database. Is there any replication mechanism (asynchronous) to push every updated transaction from every ...
1
vote
2answers
307 views

Why isn't pg_restore --create working?

I backed up a database called app_data, on a 9.3 server, with this basic command: pg_dump -n public -F custom app_data > app_data.pg.dump Then I try to restore it on another server (running 9.4) ...
1
vote
0answers
26 views

Replication of Database in PostgreSQL

I want to Replicate my PostgreSQL, in the website there far too many options, I want to know which one to choose. I have a main server which the database is stored at. Inserting, editing and deleting ...
1
vote
0answers
1k views

A “best practice” Postgresql function template for ETL processes

I use SQL Server in my day job, and have a personal project to build a star schema data warehouse in Postgres 9.4 on Ubuntu 14.04 to extend my knowledge (it'll be approximately 300GB uncompressed). I'...
1
vote
0answers
876 views

PostgreSQL 9.4 - Allow access from PHP

I would like to configure PostgreSQL 9.4 to allow connections from database users with passwords from PHP on both the command line and through Apache on a CentOS 6.6 box. I installed PostgreSQL with ...
1
vote
0answers
1k views

PgAgent unable to run a job

I am running PostgreSQL 9.4.5 on Ubuntu 14.04.3. I am trying to set up pgAgent job but I keep getting this error: DEBUG: Creating DB connection: user=xxx host=localhost port=5432 dbname=otherdb ...
1
vote
1answer
38 views

How do I uniquely update values of a text column before applying UNIQUE constraint?

I need to apply a unique constraint to a TEXT field which may or may not already contain duplicate values. If for instance I have ABC twice, I don't mind renaming them as ABC-1, ABC-2, but what if I ...
1
vote
0answers
116 views

MATLAB® export double numbers into PostgreSQL by `datainsert` (of Database Toolbox)

How is it possible to pass double numers into numeric postgreSQL columns from MATLAB? Here the list of matlab data: var1 197x1 cell var2 197x1 double var3 197x2 double var4 197x2 double ...
1
vote
0answers
3k views

PostgreSQL 9.4.4 Crashing

We are having 9.4.4 version running on linux( PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit) which is crashing unexpectedly and coming ...
1
vote
0answers
25 views

Postgres with no clients still chatty on network

This may be a non-issue but I was doing some network monitoring on my Mac and noticed that PostgreSQL (9.4.4) was still sending and receiving packets even when I'm not aware of any clients (localhost ...
1
vote
0answers
36 views

Access algorithms in Partial Index implementation of PostgreSQL 9.4?

I am studying big-O time-space complexities of different algorithms with the partial index of PostgreSQL 9.4: introsort (O(n log n)-time, O(log n)-space) and heapsort (O(n log(n))-time O(1)-space), ...
1
vote
0answers
64 views

Postgres - Error while adding constraints

Can anyone tell me what this error is? And How to resolve it? ERROR: could not read block 27651 in file "base/114689/118316": read only 4096 of 8192 bytes CONTEXT: SQL statement "SELECT fk."...
1
vote
0answers
466 views

cannot find -lpq after upgrading PostgreSQL 8 to 9 on CentOS 5

I'm trying to upgrade a (proprietary, closed-source, legacy) database-driven application from using PostgreSQL 8.1 to PostgreSQL 9.4. I have it installed on a virtual machine running CentOS 5.9. Here'...
1
vote
0answers
150 views

Can PostgreSQL with synchronous streaming replication get behind on the WAL logs?

I have 2 Postgres 9.4 servers running on 2 identical VMs that I am using to learn how to setup replication before it gets used in production. Hot standby isn't required but it would be preferred. I ...
1
vote
0answers
728 views

pg_restore fails with materialized view

I’m trying to pg_restore a db dump that contain a materialized views. If it is relevant, the materialized view is of a foreign data wrapper. It fails with the following errors: pg_restore: [...
1
vote
0answers
914 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 ...
1
vote
0answers
52 views

Error when creating Trigger (PostgreSQL 9.4)

I created a table 'Company', and I want to create a trigger so a company can only present in one city, but it can be present in several different cities in the same country. For example, there's only ...
1
vote
0answers
250 views

PostgreSQL timezone setting

While I've spent many years as a SQL developer, I'm not really a DBA, but I'm the closest thing that my company has to one. We have recently started moving many of our PostgreSQL databases from Heroku ...
1
vote
0answers
467 views

Are A or D series VMs better for deploying PostgreSQL on Azure?

In Azure there are VMs that have local storage either as spinning disk (A-series) or SSD (D-series). Considering this space is wiped on reboot... Is there a benefit to choosing the SSD option for ...
1
vote
1answer
589 views

Improve query speed for large, wide table

I have created a medical claims database using PostgreSQL 9.4.1 and pgAdmin III. There is one table with ~61 million rows and 103 columns. The table has the structure given below. It is static and ...
0
votes
0answers
23 views

Postgreslq, ¿How do I create a trigger that UPDATES, null values, under conditions?

I am doing a trigger, in which: You must first compare the "nro_persona" value of table "conductor" with table "event". If it exists, ask if the "nombre_completo&...
0
votes
1answer
8 views

pgAdmin - “cannot open logfile!” (WINDOWS 10; Postgres 9.4)

Our Windows 10 clients have both PostgreSQL 9.4 and pgAdmin3 (v1.22) installed. I keep receiving the FATAL: "Cannot open the logfile!" error by invoking pgAdmin3 in either of the following ...
0
votes
0answers
18 views

create dynamic table in store procedure without using any paramaters

CREATE OR REPLACE procedure create_table_dateWise21() LANGUAGE plpgsql as $$ BEGIN EXECUTE format(' CREATE TABLE IF NOT EXISTS test21 ( nodeip text, interface text, nodeip_interface ...
0
votes
0answers
51 views

How to take backup of PostgreSQL 9.4 database to remote server using pg_basebackup?

We want to take the logical and base backup on different server machine in the network. Currently both backups are running on same server machine causing space issues. We have the scripts in place ...
0
votes
0answers
32 views

WAL Archival not working in PostgreSQL V9.4.9 DB server

Our DB servers are on VM, during planned server restart for system patch deployment our Master DB server crashed. We have taken the clone of Primary Slave VM and restore the Master DB using last ...
0
votes
0answers
33 views

Adding key value pair to nested jsonb array object in postgresql

I have two temp tables with my_data column as jsonb type. Table temp2 has actual data lets say my_data = {"a": [ { "b": "abc", "c": "def", "id": 1 }, { "b": "efg", "c": "ghi", "id": 2 }, {...
0
votes
1answer
93 views

Postgres 9.4.5 small table grows into huge size - periodic VACUUM FULL needed

We are running a PostgreSQL database on Windows Server. The database version is 9.4.5 (it's been running stable for a long time now so we aren't updating it). Some of our tables have millions of rows,...
0
votes
0answers
1k views

Unit postgresql.service failed to load: No such file or directory

When i try to connect to my postgres database, i get the error below: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.2.1.96" and accepting ...
0
votes
1answer
182 views

How to extract the grants on default roles of PostgreSQL 10: pg_read_all_settings, pg_read_all_stats, pg_monitor and pg_signal_backend?

I have a Postgres instance of version 10 running on AWS RDS. I have a user there with default roles granted to it. I want to create the same user on my sandbox where the instance is of version 9.4. ...
0
votes
0answers
229 views

Quering Postgresql digest function inside procedure works on pgadmin but not works by calling via Laravel

recently I have created stored procedure in Postgres Sql which deals about with hashing passwords. When used via PgAdmin tool it works like a charm as stated below. I have also installed extension ...
0
votes
1answer
182 views

How to make use of WITH in PostgreSQL to reduce query cost

I am trying to get twitter data of narendramodi using below command. SELECT b.t_id,a.profile_image,b.tweet_text,e.media_media_url,b.retweet_count,b.favorite_count as like_count,count(...
0
votes
0answers
2k 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 ...
0
votes
1answer
946 views

pg_restore: [archiver] could not find entry for ID 12

I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error: pg_restore: [archiver] could not find entry for ID 12 Exactly the same happens if ...
0
votes
0answers
24 views

Simple query rephrasing for optimization

The following query: select exists(select id from avl_adc_equipo cfg where cfg.imei=354676052451142 and cfg.adc=2) Executes pretty fast, but as it ...
0
votes
0answers
16 views

Increase XIDWraparound limit

My Database is having huge data > 5TB, at some point I am doing vacuuming few tables on daily basis which bit hustle for the server. And I can't do purging to get some room for new tuples due to some ...
0
votes
0answers
188 views

Upgrading PostgreSQL 9.4.9 to 9.6.x on Debian

Current version: PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit OS: Debian GNU/Linux 8.6 (jessie) Any considerations and/or recommendations on ...
0
votes
0answers
335 views

Comparing explain plains and execution time

Please consider the following 2 queries (which yield the same result), and their corresponding explain plan output: Query A: select max(registro) from telemetria_data.historico_353871042915279 ...
0
votes
0answers
43 views

Final Unique Index for Person and Companies?

I am creating a persons schema where the the person table must be very simple because it will be father of other tables that completes it, but a person can be fisical or legal, so I wont put any ...
0
votes
1answer
113 views

Having two data folder in my postgresql setup- Postgresql

I am using centos 6 and installed Postgresql_9.4 using the following commands yum install postgresql postgresql-contrib postgresql-client pgadmin3 yum install postgresql-server After that i ...
0
votes
0answers
2k views

Cannot drop constraint due to FK references

I have detected a duplicate index on an id field on one of our tables, as that column is the PK, but there's also a separate UNIQUE index on such column, which is useless. But I cannot delete the ...
0
votes
0answers
813 views

Query hung or way too long?

As part of the proccess of new items added to our system, some tables and relations are created programmaticaly via trigger, but it seems to be failing quite often right now. The DDL seems to be ...
0
votes
0answers
1k views

PostgreSQL crash when connection failed

Error log is: 016-02-21 21:22:34 GET DETAIL: Connection matched pg_hba.conf line 83: "host all all ::1/128 md5" .... .... 2016-02-21 21:22:37 GET DETAIL: ...
0
votes
1answer
170 views

PostgreSQL 9.4: Index Only Used For Lower INET Ranges

For some reason queries on the high range of are extremely slow and queries on the low range are extremely fast. My index is an adapted version of the answer in Optimizing queries on a range of ...
0
votes
2answers
62 views

How Can I Access the postgres database in a Script Executing pg_dumpall?

I want to run the following script using my role which includes the postgres database. pg_dumpall -c -f ~/Desktop/pgdumpall_proddb_`date +%Y%m%d_%H%M%S`.sql Here are the settings in pg_hba.conf: # ...
0
votes
0answers
289 views

How autocommit = 1 impacts statements between BEGIN/COMMIT?

Let's say I have following code: begin; set autocommit = 1; update tbl1 set col1 = 1; update tbl1 set col1 = 2; commit; What would be the difference if autocommit (line 2) was set to 0?
0
votes
0answers
532 views

ERROR: duplicate key value violates unique constraint “repl_stat”

Error messages constantly showing up in log files and I don't know what to do with them: 2015-11-26 11:08:05 MSK ERROR: duplicate key value violates unique constraint "repl_stat" 2015-11-26 11:08:05 ...
0
votes
0answers
206 views

Proper handling of errors on pg_restore

Following the execution of pg_dump -Fc the follow-up pg_restore is generating errors. Under pg_restore -c -v -1 -d the following error is generated Error while PROCESSING TOC: The motive is: ...