All Questions
Tagged with postgresql-9.0 postgresql
18 questions
1
vote
1
answer
73
views
Do I need to CREATE LANGUAGE plpgsql in PostgreSQL 9+
I have a PHP web application using a PostgreSQL database.
Ideally, to install this web application should be easy and only require a database name and user.
Then, the web application loads an SQL file ...
- 21
0
votes
0
answers
63
views
How do I successfully migrate a (very) old PostgreSQL database with PostGIS objects to a new version of PostgreSQL and PostGIS?
I've been trying to migrate a PostgreSQL database on version 9.0.1 with PostGIS objects to Postgres version 12.9 (both on linux) and I've been encountering numerous errors. I believe the source ...
- 694
0
votes
1
answer
69
views
How can I find or log inbound traffic connecting to a PostgreSQL database instance?
I have a postgres instance where I need to find out who/what is connecting. The installed version of postgres is 9.0.1. The instance sits on a linux box, SUSE I believe. Where could I look if it is ...
- 694
9
votes
3
answers
26k
views
Postgres ERROR: tuple concurrently updated
I have a large table test in which in user_id 2 have 500000 records. So I want to delete this record in chunks of 100 records but it is given error.
Here is my query:
delete from test where test_id ...
- 642
0
votes
1
answer
476
views
Postgres: Missing data after Binary Copy - Is VACUUM FULL required?
I'm running Postgres 9.0 on a relatively big (10TB) and old Database. I must move that database to new server hardware, so I copied the whole $PGDATA directory via rsync of a LVM Snapshot and used ...
- 21
3
votes
1
answer
6k
views
Add column with a sum total to crosstab() query in PostgreSQL 9.0
Following on from my previous question:
Creating crosstab() pivot table in PostgreSQL 9.0
I managed to create a pivot table for ageband using the crosstab() function. I can use this to either create ...
- 145
5
votes
1
answer
4k
views
Creating crosstab() pivot table in PostgreSQL 9.0
I have a rather complicated issue for myself which I hope someone can help me with.
I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table.
To keep it simple I will ...
- 145
3
votes
1
answer
10k
views
Find substrings within between 2 string fragments
I am trying to populate a view in PostGIS in Postgres 9.0 and in this view I want it to contain a substring based on 2 string positions. See below for my code.
CREATE OR REPLACE VIEW vw_actions AS
...
- 145
0
votes
1
answer
125
views
Postgres 9.0 recovery from inadvertent drop-all-tables event [closed]
Due to a accidental config file check-in, a unit test wiped out our entire production database (did a drop, then re-created the tables). Trying to restore from backup revealed the recent backups were ...
- 411
5
votes
3
answers
5k
views
How to reclaim disk space after delete without rebuilding table?
Our PostgreSQL 9.0 Windows production server is running low on space.
In our 100GB database, we have a large table containing TOASTed binary data. We have deleted some rows and need to return the ...
- 301
5
votes
2
answers
105k
views
PostgreSQL Error: server closed the connection unexpectedly
I cannot connect to PostgreSQL server from PgAdmin III , so I get this error:
Error connecting to the server: server closed the connection
unexpectedly
This probably means the server terminated ...
- 201
2
votes
3
answers
2k
views
Vacuum settings for mostly append-only data
I have a table with the following characteristics:
We INSERT a few 100k rows each day
We never UPDATE the rows
We DELETE "old" data once a week
From my shallow knowledge of Postgres VACUUM, it seems ...
- 123
2
votes
1
answer
203
views
Setup Master-Slave replication with DML changes in Slave
I have a master database (PostgreSQL 9.0) and I need to setup a streaming replication on a slave. Slave db can read/write and make schema level changes too which shouldn't write back to master. Can ...
- 21
1
vote
1
answer
3k
views
Postgresql querying trends
Firstly apologies if this is a duplicate, I am fairly new to SQL and so Im not sure what the correct terminology to use in my searches
So I have a database which records motor races, with the ...
- 319
14
votes
2
answers
11k
views
Select longest continuous sequence
I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specific column.
Consider the following table:
lap_id (serial), lap_no (int), car_type (...
- 319