I built a new server running centos 6.4 and postgresql 8.4. I backed up all the
databases from the old server running fedora and postgresql 8.1 using this
script.
#!/bin/bash
# Backup all Postgresql databases
# Location of the backup logfile.
logfile=/var/lib/pgsql/backups/logs/pg_back.log
#
, how do I migrate the databases with their tables
and data and migrate the latin1 encoded databases?
Thanks
From: pgsql-admin-ow...@postgresql.org
[mailto:pgsql-admin-ow...@postgresql.org] On Behalf Of Marc Fromm
Sent: Thursday, October 10, 2013 1:17 PM
To: pgsql-admin@postgresql.org
Subject
8.4 just because it's what gets installed with CentOS6.4.
From: Steve Crawford [mailto:scrawf...@pinpointresearch.com]
Sent: Thursday, October 10, 2013 1:55 PM
To: Marc Fromm; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] move dbs from 8.1 to 8.4
On 10/10/2013 01:17 PM, Marc Fromm wrote:
I built
, 2013 1:55 PM
To: Marc Fromm; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] move dbs from 8.1 to 8.4
On 10/10/2013 01:17 PM, Marc Fromm wrote:
I built a new server running centos 6.4 and postgresql 8.4. I backed up all the
databases from the old server running fedora and postgresql 8.1 using
I need to convert a bunch of my databases to utf8 before I can migrate them to
postgresql 8.4. Is there a command that during the pg_dumpall process the
encoding can be changed from latin1 to utf8?
This is the error I get when migrating the databases from 8.1 to a machine
running 8.4
Is there a way to create a select statement that will select a record if the
exact term is found in a field that contains the text to describe something?
If I create a select statement using WHERE description LIKE 'art' I get every
record that has words like depart, start and so on.
If I create
Thanks Tom, I just discovered that. I reworked the php so the quotes surround
the regexp.
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Thursday, March 28, 2013 1:07 PM
To: Marc Fromm
Cc: Craig James; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] select exact
Can I use RETURNING id in an insert statement with postgresql version 8.1.10?
I cannot find a clear example on how to use it to capture the id created by the
insert statement into a variable in PHP.
Many examples are like this but don't state how to access the returned id:
INSERT INTO Addresses
I am trying to get the id of the current inserted record. The field name is
'id' and it is a primary key. I am obviously missing the correct syntax.
I cannot use RETURNING id.
$sql = INSERT INTO jobs (job_title, num_positions, pay_min, pay_max,
Is there a way to query a unix timestamp date? In the database the orderdate
field is a unix timestamp.
I would like to create the where clause to a query on a specific date like
December 17, 2010.
Select * from orders where orderdate = '12/17/2010';
Thanks
Marc
?
GRANT SELECT ON TABLE sql_languages to tom;
ERROR: relation sql_languages does not exist
Thanks
Marc
Marc Fromm
Information Technology Specialist II
Financial Aid Department
Western Washington University
Phone: 360-650-3351
Fax: 360-788-0251
I am running postgres on a red hat linux server.
postgresql-python-8.1.23-1.el5_6.1
postgresql-test-8.1.23-1.el5_6.1
postgresql-libs-8.1.23-1.el5_6.1
postgresql-docs-8.1.23-1.el5_6.1
postgresql-contrib-8.1.23-1.el5_6.1
postgresql-8.1.23-1.el5_6.1
postgresql-pl-8.1.23-1.el5_6.1
simple but I cannot seem to see it.
-Original Message-
From: barb...@bariloche.com.ar [mailto:barb...@bariloche.com.ar]
Sent: Tuesday, February 08, 2011 4:57 PM
To: Marc Fromm
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] phpPgAdmin configuration
Hello:
Is it a passwordless account? I
[mailto:guilla...@lelarge.info]
Sent: Tuesday, February 08, 2011 3:36 PM
To: Marc Fromm
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] phpPgAdmin configuration
Le 09/02/2011 00:27, Marc Fromm a écrit :
Yes, the database server is on the same server as the http server.
The complete error message is just
I installed phpPgAdmin on my red hat box in the /var/www/html directory.
I edited the pg_hba.conf file as many tutorials stated with the lines.
local allalltrust
host allall127.0.0.1/32 trust
When I
] On Behalf Of Guillaume Lelarge
Sent: Tuesday, February 08, 2011 3:04 PM
To: Marc Fromm
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] phpPgAdmin configuration
Le 08/02/2011 23:23, Marc Fromm a écrit :
I installed phpPgAdmin on my red hat box in the /var/www/html directory.
I edited
I was checking the details to an update to postgresql 8.1 and the following
message was on the release notes page:
The PostgreSQL community will stop releasing updates for the 8.1.X release
series in November 2010. Users are encouraged to update to a newer release
branch soon.
There were no
In my postgresql logs, found in the pg_log folder, there are several sql syntax
errors where the sql statement is broken up with the characters ^M. The error
is at the first field name that is broken up by the ^M as shown below in the
example. The ^M breaks up the field reference2zip by
I created a new database and I want to copy a table from a different database
into the new database.
1. I used phpPgAdmin and exported the table that I want a copy of.
2. In the new database I pasted the export into the SQL box in phpPgAdmin and
clicked execute to create the table.
The table was
I need to upgrade postgresql from 8.1.11 to 8.1.18.
The documentation states to
REINDEX all GiST indexes and REINDEX hash indexes on interval columns after the
upgrade.
I googled the two items only to find the same comment with no instructions on
what they are or how to do it.
How can I check
I have a column that contains user email addresses. I need to update all email
address in the column that end with @cc.edu to @ss.edu and retain the
information (the user name) that exists before the @ symbol. Is there an update
query that can edit part of a column record?
Marc
I am receiving this error when I try to add new records:
INSERT INTO public.classification_guide_cats (id, cat_title) VALUES
(nextval(('classification_guide_cat_id_seq'::text)::regclass), 'temp')
SQL error:
ERROR: duplicate key violates unique constraint
classification_guide_cats_pkey
id is a
]
Sent: Wednesday, June 17, 2009 10:10 AM
To: Marc Fromm
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] error: duplicate key
On Wed, Jun 17, 2009 at 10:51 AM, Marc Frommmarc.fr...@wwu.edu wrote:
I am receiving this error when I try to add new records:
INSERT
Some of our users use an ODBC connection between MS Access and an Oracle
database to work with the data.
Access however has a 255 field/column limit, which the Oracle Db has now
execeded.
Is there a way to make an ODBC connection between a postgresql db and the
oracle DB and pull all the data
If I gather the sql results with this code
$results = pg_query($dbconn,$query);
I can check if there is no returned data with this code
$rows = pg_fetch_assoc($result);
but if I then use a while loop to display data (if there is data returned) with
this code
while ($row =
My results are missing the first record as you explained.
-Original Message-
From: iog...@free.fr [mailto:iog...@free.fr]
Sent: Friday, January 02, 2009 10:09 AM
To: Marc Fromm
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] access data in php
On Fri, 2 Jan 2009, Marc Fromm wrote
: Marc Fromm; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] access data in php
On Fri, Jan 2, 2009 at 11:09 AM, iog...@free.fr wrote:
pg_fetch_assoc behave like pg_fetch_array: it increments the internal
pointer to the current result.
So if you call it once, then pg_fetch_array will return
I created this table:
Column | Type | Modifiers
++
first_name | character varying(20) |
last_name | character varying(30) |
w_number | character varying(9)|
alert | character
actually explaining what edits to make. I made no edits and it
appears the data is all restored that was missing from the departments table.
-Original Message-
From: val [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 04, 2008 5:57 AM
To: pgsql-admin@postgresql.org; Marc Fromm
Subject
How do I restore just a table to a database? I was able to create a backup of
the required table from a backup of the database as follows.
I don't know how to properly restore the backed up table departments to the
original sms database.
create a new db
createdb -U postgres smstest
restore a
I need to drop a role/user but receive the message:
ERROR: role ken cannot be dropped because some objects depend on it
DETAIL: 1 objects in database css
1 objects in database manuals
1 objects in database cswe2
I checked the databases listed and the user/role ken is not associated with any
I have the same postgresql databases on two different servers.
Boteh servers have the same version of postgresql, 8.1.
The following backup command creates a file twice as big on one server,
compared to the other server.
pg_dumpall -c -U postgres | gzip alldb.gz
Red Hat 5EL: alldb.gz is 29MB
I submitted a post about my pg_dumpall file being twice as big on one server
compared to the other.
It turns out that one specific database called postgres is growing each time I
perform a restore from the pg_dumpall files.
The database postgres has gone from 5.1MB to 10MB to 15MB to 20MB. No
I started logging on our server. There are many entries like this:
transaction ID wrap limit is 1073768178, limited by database postgres
transaction ID wrap limit is 1073771864, limited by database sms
Each database has several of the above entries.
Also there are these fatal entries:
FATAL:
I just started logging postgresql. In the log are these entries:
478 LOG: transaction ID wrap limit is 1110972072, limited by database
cswe2
479 LOG: transaction ID wrap limit is 1110972072, limited by database
cswe2
480 NOTICE: number of page slots needed (27072) exceeds
We would like to log sql activity from our web pages that use postgresql
databases.
I read the documentation for 8.1 at postgresql.org. The postgresql.conf file
under the -Where to log- section has
#log_destination = 'stderr'
Is stderr the default or do I need to remove the comment symbol to
I installed Red Hat 5.2 EL. During the install I select postgresql and this
versoin was installed: postgresql-8.1.11-1.el5_1.1.
1. I started postgresql as follows:
initdb -D /var/lib/pgsql/data
2. I started the database server as follows:
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
Does anyone have some insight on why the db size is expanding with each restore?
If I restore all the postgresql databases from pg_dumpall and use the -c to
drop databases before restoring them the size of the base directory
dramatically increases with each restore (193MB to 355MB to 624MB). If
I backed up all my databases using:
pg_dumpall -O -c -U postgres /tmp/pgalldb2
-O to remove owners on all objects
-c to drop databases before recreating them during the restore to prevent
duplicate records
I restored all the databases with this command
psql -U postgres -f /tmp/pgalldb2 postgres
If I restore all the postgresql databases from pg_dumpall and use the -c to
drop databases before restoring them the size of the base directory
dramatically increases with each restore (193MB to 355MB to 624MB). If I run
vacuumdb, it only drops by a few MB.
If I do the long process by:
1.
I am currently running these to commands in scripts to backup the postgresql
databases.
/usr/bin/pg_dumpall -ch 127.0.0.1 -U postgres | gzip
$backup_dir/postgresql-all-$timeslot-databases.gz
/usr/bin/pg_dump $i -ch 127.0.0.1 -U postgres | gzip
$backup_dir/postgresql-$i-$timeslot-database.gz
I am trying to run commands from the shell (without becoming the postgres user
first), which work fine on our live server, but on our dev server I receive
FATAL authentication errors.
[EMAIL PROTECTED] tmp]$ createdb -U postgres lan_portal
createdb: could not connect to database postgres:
WE have a db with a column/field of type character varying set to a
length of 1000. A user attempted to enter data into this column/field
breached the 1000 limit. When the user submitted the form the data was
not entered into the database and no error or message was displayed.
Is there a way to
One of our databases when viewed in phpgadmin does not display the
actions on one of the tables in browse mode. I changed ownership of the
database to postgres and still the one table does not display the
actions.
Is there a command I can run to enable the Actions on this table?
Thanks
Marc
I am backing up my databases with pgdump -c command to prevent duplicate
records during a restore.
When I restore the database with this command:
psql -U postgres infoserv /tmp/infoserv-03-25-2008_12-10
I get the message to use cascade to drop dependent objects.
DROP TABLE
ERROR:
Is there a better way to create a database from the schema of another
database, to have a copy of a database without the data?
1.) I get the schema from a database
pg_dump -s -U postgres -O databasename /tmp/template_name
2.) I create a new database.
createdb -U postgres newdb
3.) I restore
I received this message after running vacuum on a database.
NOTICE: number of page slots needed (27296) exceeds max_fsm_pages
(2)
HINT: Consider increasing the configuration parameter max_fsm_pages
to a value over 27296.
The documentation for 8.1 sates:
This setting must be more than 16 *
When I restore from a pg_dumpall file, I am required to peform the following
steps or else all the records in the databses are duplicated. If I only empty
the data/base folder I get al sorts of errors. I find in necessary to wipe the
entire data folder and rebuild it with initdb. Is there a
Marc Fromm
Information Technology Specialist II
Financial Aid Department
Western Washington University
Phone: 360-650-3351
Fax: 360-788-0251
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
using the command service postgresql start starts postmaster but the
process (ps ax | grep post) is listed as /usr/bin/postmaster -p 5432 -D
/var/lib/pgsql/data
And both are the same thing?
Thanks
Marc Fromm
Information Technology Specialist II
Financial Aid Department
Western Washington University
We have a database that we use each year.
Each year the database needs to be free of any data from the past year.
We need to retain last years data base with its data.
I tried using template:
CREATE DATABASE 'year2007-2008' TEMPLATE 'year2006-2007';
But the new database contains all the data
of
postgres, but it is not mentioned in the documentation?
What is the correct way to stop and start postgresql?
thanks
Marc Fromm
Information Technology Specialist II
Financial Aid Department
Western Washington University
Phone: 360-650-3351
Fax: 360-788-0251
--
Sent via pgsql-admin mailing
52 matches
Mail list logo
