People who code: we want your input. Take the Survey

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
24
votes
1answer
33k views

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables

What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables or pgstattuple?
1
vote
3answers
32k views

Opening a .backup file for PostgreSQL

I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise. In short, I have what I think is a ...
3
votes
1answer
367 views

Multiple databases, in multiple geographic locations while preserving PostgreSQL triggers and data consistency

I've read a ton over 300 pages of db setups in the last day but I feel a little overwhelmed with all of the potential options and lack of documentation on how PostgreSQL triggers function across ...
1
vote
3answers
5k views

access columns returned by psql from bash

I want to use value returned by psql in bash. This is the script: psql "connection parameters" -c "SELECT pg_database_size('dbname');" The output is like this: pg_database_size ------------------ ...
1
vote
1answer
2k views

How many rows were copied from table

I want to write a script to backup a table using COPY. psql "connection parameters" -c "COPY (SELECT * FROM tbl WHERE insertion_date > 'date') TO STDOUT WITH CSV HEADER;" | bzip2 -c > backup....
97
votes
5answers
192k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
4
votes
1answer
6k views

PostgreSQL Scheduled Database Backup

For scheduling a daily back up on a postgres database we execute the following command: 0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_name \ > $HOME/db_backups/database_name-`...
8
votes
1answer
9k views

Dropping unique constraint on PostgreSQL table taking 10+ hours

We have a PostgreSQL database with a fairly large table (~50 million rows). The table has a unique constraint across several columns, each of which is indexed. For context, I recently deleted about ...
2
votes
1answer
3k views

How to import the data of postgres 8.x to postgres 9.x

My OS is windows 7 x64. I was use postgres 8.4, and all databases are in postgres/8.4/data. Yesterday, I reinstalled my os system, but I found I can't install postgres 8.4 anymore (there is always an ...
12
votes
1answer
19k views

How to get actually allocated tablespace size by each postgres database?

Postgres tablespace is cluster-wide. Therefore, how do I caculated how much space allocated by each database in postgres tablespace ?
6
votes
2answers
4k views

Is Tablespace something like Logical Volume?

I'm confused about tablespaces in PostgreSQL. Is it something like LVM? I mean when disk is getting full, can we add another disk, format and then CREATE TABLESPACE tblspace LOCATION /media/disk2/data?...
2
votes
1answer
3k views

Triggers not being run on a partitioned table

I'm using PostgreSQL. I have a table that I have partitioned into three sub-tables - and as far as I can tell, this all works correctly. Rows are correctly inserted into the appropriate partition and ...
2
votes
1answer
707 views

PostgreSQL: Trouble accessing another server's Data via NFS

Background: I'm working on a 2-node High Availability PostgreSQL database cluster for my company. The system has a master/standby setup where the standby takes over the duties of the master when the ...
4
votes
2answers
836 views

Huge database logging of event type rows and ways to optimize it

We have a database that stores events. Events are generated at a 1000 per sec rate. We need to keep these events accessible for some years. Usual use of these events is selecting some of them from the ...
4
votes
3answers
6k views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I have installed oracle_fdw successfully. After I create extension oracle_fdw and foreign table it shows the following error. Is this a server configuration problem? --env Oracle : 10.2.0.1 ...
0
votes
1answer
348 views

Update Postgres on server that already has version included with system

Mac OS X (Lion) Server includes Postgres (9.0.5) that it uses for some system utilities (Wiki server, caldav, etc.). I'm about to start using this server for some Rails applications and am wondering ...
8
votes
1answer
4k views

PostgreSQL Failover - What tools should I use?

Here is the scenario: There are two machines running CentOS 6.2 - machine0 and machine1 Both have PostgreSQL 9.1 installed. One of them should be active, as a master system and through asynchronous ...
1
vote
1answer
174 views

Impact of changing schema name

I have a server with two databases. Each database has a different schema name despite the fact that DB2 is a subset of DB1 (DB2 has a subset of the tables in DB1). Not withstanding any code changes ...
5
votes
4answers
3k views

Trouble setting up Postgres for the first time

I have some, albeit limited, experience with MySQL and MongoDB (both running fine on server) but having trouble with Postgres and find the documentation lacking for newbies, like myself. Postgres is ...
15
votes
1answer
12k views

Postgresql performed a “fast shutdown” by itself. Why? How was it able to do that?

I've already googled and manual read as much as I could before posting this here. Version info: Postgres8.4 on Ubuntu10.x I have a personal laptop that I run a postgres server on, and a Java script ...
3
votes
2answers
16k views

Joining based on date (or date range)

I'm not sure if I've completely fudged the design of my small hobby database (I'm not a DBA by any means), but I have a table like this (primary key is (staffid, effectivefrom)): staffid | target | ...
1
vote
1answer
352 views

Postgres shared_buffers and pooled processes

I have read several articles (including Postgres docs) that suggest setting the shared_buffer's config value to 25% of your total physical memory. Using my 4 GB memory as an example... If I ...
0
votes
1answer
132 views

What DML commands can be allowed / limited on a reporting server?

I have a largish database (PostgreSQL 9.1) which I'm preparing to replicate using streaming replication. The 2nd server will be used for reporting and backup purposes. When building reports, it's ...
2
votes
1answer
5k views

Postgres: trying to fix some unicode encoding issues

I've got a postgres 8.4 database with a few mangled characters in one of the columns on a table. For example, the word "don't" gets displayed within psql as: donâ\u0080\u0099t I want to be able to ...
11
votes
2answers
2k views

PostgreSQL Complete Database Backup [duplicate]

I've been trying to figure out how I can completely backup my PostgreSQL database. I have not seen any resource that explains how to do a complete backup. I am aware of the pg_dump and pg_dumpall ...
2
votes
2answers
1k views

Attempting to avoid SORT in query planner

I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically): ...
2
votes
1answer
1k views

PostgreSQL partitioning query performance inexplicably bad

I find this a bit mysterious. The cost of getting the maximum value out of just the partition parent table (which is empty) is very cheap: evlampts=# explain select max(timestamp) from only mcdata; ...
3
votes
2answers
3k views

Postgresql 9 table lock resolution

I have an ALTER TABLE statement that according to pg_stat is "waiting", and I'm wondering if there is an easy fix to allow the statement to execute? From my limited understanding I think it's a ...
13
votes
1answer
663 views

Disk file effects of delete and vacuum

I have a very frequently updated table with 240 million rows (and growing). Every three hours 1.5 million rows are inserted and 1.5 million are deleted. When I moved the cluster to a SSD this bulk ...
2
votes
1answer
458 views

Slow enumeration of schemas and tables in a largish database

We have a Postgresql 8.4.8 database server on a Debian system with a database that has 500+ schemas with roughly 300+ tables each. The schemas and tables are created by scripts, we cannot influence ...
5
votes
1answer
4k views

WAL backups with pg_dump not possible?

I am hoping to move to WAL backup from pg_dump. But I am confused by this note in 4.3 documentation: Perform the backup, using any convenient file-system-backup tool such as tar or cpio. And in ...
48
votes
4answers
35k views

Does running pg_dump on live db produce consistent backups?

I have a 3GB database that is constantly modified and I need to make backups without stopping the server (Postgres 8.3). My pg_dump runs for 5 minutes. What if the data is modified during the process?...
2
votes
1answer
3k views

PostgreSQL dump/restore from 8.4 to 8.1 in a shell script

I have to restore a DB table every day from a postgresql 8.4 to 8.1. For the backup, I use this code (windows server): D:\PostgreSQL\8.4\bin\pg_dump -U postgres -p 5433 -t my_table myDB > D:\...
7
votes
2answers
3k views

Database for building a realtime analytics system

I want to build a system similar to Google Analytics (only used for internal use, less traffic and less feature), and mainly focus on Real time counting of unique URI visit/PV by different dimensions ...
2
votes
1answer
8k views

Error importing back a postgres db after dumping

I dumped my postgres db using pgAdmin in plain format because I wanted to search for some text on it. After doing the search, I decided to import the db back from the sql query window but I get this ...
2
votes
2answers
3k views

Slony lib not found

Trying to run the example slony config script in the documentation but getting the following error: postgres$ /tmp/slonik_example.sh <stdin>:8: PGRES_FATAL_ERROR load '$libdir/slony1_funcs'; ...
13
votes
2answers
14k views

Fastest validation query in PostgreSQL

What is the fastest query in PostgreSQL, which I can use as a validationQuery binding a JNDI resource? I thought that SELECT 1 is the simplest, but in this document it is said that in PostgreSQL we ...
22
votes
2answers
44k views

How do I restore a plain text postgres .backup file

I exported a postgres db from pgAdmin in a plain format because I wanted to search the whole db text. I then tried to restore the database back but it gives an error saying that the archive file is ...
41
votes
3answers
48k views

Idiomatic way to implement UPSERT in PostgreSQL

I've read about different UPSERT implementations in PostgreSQL, but all of these solutions are relatively old or relatively exotic (using writeable CTE, for example). And I'm just not a psql expert ...
5
votes
2answers
4k views

Span single transaction over several sequential psql sessions

I am running PostgreSQL 8.4 database server and psql terminal front-end on Ubuntu 10.04 Lucid Lynx and would like to span a single transaction over several sequential psql sessions. When I connect to ...
1
vote
1answer
320 views

Slony not replicating changes from a dump file

I’m working on a project where we are using pg_dump to get the data out of one database and then using psql to load it into another. The second database is the master db in a Slony replication ...
0
votes
1answer
220 views

How to improve restoring the PostgreSQL database?

I'd like to restore the PostgreSQL database from a dump. The below command works fine for me: CALL "%PGBIN%\pg_restore" -c -d NetCatalog %DUMPFILE% How can I add a few lines to my .bat file to ...
3
votes
1answer
9k views

How to select bytea data as binary data, inserted as hex-encoded from binary

The post How to insert (file) data into a PostgreSQL bytea column was helpful. However, when I select these columns, I get the same hex encoded data. For instance: > select substring(smallblob,0,...
2
votes
1answer
2k views

Postgres - how to randomize two columns in a table

I have a table of User data which I would like to disguise/anonymize. Two of the columns have location data (e.g. lat and long, both BigDecimals). I have anonymized all the names and other ...
3
votes
3answers
2k views

Is there a PostgreSQL proxy running queries on several servers at once?

Let's assume we had PostgreSQL version X.Y and decided to give a test-try to version Z.W. Probably, there are some changes between X.Y and Z.W that could cause a little bit of trouble :-) It would be ...
2
votes
1answer
3k views

Table structure/schema for spreadsheet-like web app (ex: Google Docs)?

I'm a front-end developer/designer who is trying to branch out and learn more about the back-end world. I've chosen Python, Django, and Postgres as my starting place. My goal is to build a personal ...
2
votes
2answers
2k views

pg_restore fails for a pg_dump from a db that worked just fine

Any idea how this can happen? I have a table which I created by loading data into it via the COPY command. A column in it was declared as VARCHAR(200). I dumped the table, moved it to another computer,...
3
votes
2answers
241 views

Validation of availability for a new order

I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each ...
1
vote
1answer
8k views

Postgres Administration roles

I need to work with a remote postgresql server. My application needs to perform the following actions: to be able to create/drop database (I only need to work with 1 specific database) to be able to ...
5
votes
1answer
1k views

Dropped database still shows but causes errors PostgreSQL

I had dropped a database a couple weeks ago but it still shows with \l. I've tried using DROP DATABASE mixmobi_master; and the first time it gave me a confirmation message but mixmobi_master was ...

1
261 262
263
264 265
269