Re: [GENERAL] pg_restore: custom archiver unexpected end of file , ERROR: missing data for column

[Ziggy Skalski]

On 2014-06-17, 5:33 AM, Khangelani Gama wrote:


Hi

I keep getting the following error when trying to restore a database 
on Postgres 9.2.4.


I used pg_dump –Fc with no owner from one server (8.4.2).  It’s 
working fine for other similar databases.


And then restoring the database on another server running on 9.2.4 
using pg_restore –Fc. I get the get following error but I don’t get 
any errors when I dump the Database.


pg_restore: custom archiver 
 
unexpected end of file

ERROR: missing data for column "fpp_cde"
CONTEXT: COPY audit, line 2565159: "4585427 21~21~21 5143 0 2013-12-01 
15:03:18.336718 \r\nSTATEMENT: COPY audit (aud_id, grp_cde, act_typ, aud_ver, usr_id, 
aud_device_id, aud_ts, aud_xml, aud_replication, fpp_cde, aud_doc_no, 
aud_doc_ts, br_cde, obo_br_cde, obo_aud_id, orig_trf_aud_id, 
orig_trf_br_cde, aud_build_number) FROM stdin;


but there is data for a column called fpp_cde

SELECT fpp_cde from audit where aud_id =4585427;

fpp_cde

-

201311

(1 row)

Please help ,

Kind Regards,

Khangelani

CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee 
only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any 
review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intended 
addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries 
distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other than 
strictly for business purposes.




Hi there,

Did you do the pg_dump using 9.2 binaries or the 8.4 binaries?  It's 
recommended that you always use the newer binaries for dumping. Also, 
have tried to do a pg_upgrade instead of the dump/restore?


Ziggy

Re: [GENERAL] pg_restore: custom archiver unexpected end of file , ERROR: missing data for column

[Ziggy Skalski]

On 2014-06-17, 8:08 AM, Khangelani Gama wrote:


I keep getting the following error when trying to restore a
database on Postgres 9.2.4.

I used pg_dump –Fc with no owner from one server (8.4.2).  It’s
working fine for other similar databases.

Hi there,

Did you do the pg_dump using 9.2 binaries or the 8.4 binaries?  It's 
recommended that you always use the newer binaries for dumping.  Also, 
have tried to do a pg_upgrade instead of the dump/restore?


No I was using 8.4 binaries…Thanks, I will try that now.  we will be 
upgrading the 8.4 server after converting the service into another 
operating System .


Sorry I am not knowledgeable about this , I copied the binary file 
onto the server where postgres 8.4 is running, untarred it (file is 
postgresql-9.2.4.tar.gz). But I can’t find the pg_dump script path. It 
looks like I have to configure it and do the gmake n gmake install. 
But how do I do that without affecting the current running postgres 
version 8.4.


CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee 
only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any 
review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intended 
addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries 
distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other than 
strictly for business purposes.




Hi,

This part of the documentation describes how to apply appropriate 
switches to your configure to place the 9.2 install into alternate location:

http://www.postgresql.org/docs/9.2/static/install-procedure.html

Re: [GENERAL] Problem with connection

[Ziggy Skalski]

On 13-06-19 04:13 PM, karen chau wrote:

Hi Folks,
I'm having trouble with connection / remote connection.  I'm running 
Postgres on Solaris 10.


$ netstat -a |grep 5432
localhost.5432 *.*0  0 49152 0 LISTEN
6002ae2ee98 stream-ord 6002af1d080  /tmp/.s.PGSQL.5432

_Works fine_
$ psql -d director
Welcome to psql 8.1.19 (server 8.2.17), the PostgreSQL interactive 
terminal.


_*Does not work*_
$ psql -h phys-brmtso-2 -d director
psql: could not connect to server: Connection refused
Is the server running on host "phys-brmtso-2" and accepting
TCP/IP connections on port 5432?

I tried both md5 & trust.
phys-brmtso-2# tail -15 pg_hba.conf
# superuser. If you do not trust all your local users, use another
# authentication method.


# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD

# "local" is for Unix domain socket connections only
local   all all   trust
# IPv4 local connections:
#hostall all 127.0.0.1/32  trust
hostall all 127.0.0.1/32  md5
# IPv6 local connections:
hostall all ::1/128   trust
# remote connetions:
hostall all 10.195.1.0/24  md5




Hi,

Check this line in your postgresql.conf maybe?

#listen_addresses = 'localhost'# what IP address(es) to listen on;

You should have it set to localhost *and* the hostname you want to use 
(i.e. the phys-brmtso-2), that might be the problem I think.


Ziggy

Re: [GENERAL] .pgpass being ignored

[Ziggy Skalski]

On 13-06-21 06:19 PM, Stephen Rasku wrote:

I am trying to write a script that will create and populate a
database.  I don't want to enter a password every time so I want to
use a .pgpass file.  It has the correct permissions:

 $ ls -l $PGPASSFILE
 -rw---  1 Stephen  staff  43 21 Jun 14:48 /Users/Stephen/.pgpass

However, when I call createdb, it fails:

 $ createdb -h 192.168.1.4 -U postgres --no-password JobSearch
 createdb: could not connect to database postgres: fe_sendauth: no
password supplied

This is the contents of my .pgpass file:

 192.168.1.4:5432:DatabaseName:postgres:thisIsTheCorrectPassword

If I omit the --no-password option it will prompt me for a password
and the command will succeed.  I am using 9.0.10 from MacPorts.

What am I doing wrong?

...Stephen




Hi,

Just going from a personal experience, have you tried to open the 
.pgpass file in vi and made sure there's no trailing spaces in your 
pgpass entry?  That bit me once before :)


Ziggy



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Problems with adding IP to pg_hba.conf

[Ziggy Skalski]

On 2013-08-28 2:00 PM, Chrishelring wrote:

Hi all,

I need to let a server access our postgreSQL database but I´m having
problems with the configuration. I´m getting the error message below,

psql: FATAL:  no pg_hba.conf entry for host "10.24.17.22", user "k175",
database "k175", SSL off

I´ve added the section below to the pg_hba.conf


# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD
# IPv4 local connections:
hostall all 10.24.17.0/24   
  md5

Still resulting in the above error. Can somebody guide me to what is wrong?

Thanks

Christian



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Problems-with-adding-IP-to-pg-hba-conf-tp5768809.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.



Are you sure you reloaded the config after making the above change?   
(happened to me couple times before, so worth a try :)


Ziggy



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] pg_upgrade & tablespaces

[Ziggy Skalski]

On 13-12-19 11:34 AM, Joseph Kregloh wrote:

Hello,

I am trying to upgrade from 9.0.14 to 9.3. I am using the pg_upgrade 
utility. I need to use pg_upgrade because my production database is 
800GB+ and with over 80 tablespaces and doing an export from 9.0 and 
importing to 9.3 would take at least 2 days.


Currently I am testing on the development database which is only 100GB 
with a same number of tablespaces. I am working on FreeBSD with jails. 
So one jail contains 9.0 and the other 9.3. In the 93 jail I mount the 
data and binary directories for the 9.0 jail.


Here is the command to check:
pg_upgrade -b /home/jkregloh/pg_bin/ -B /usr/local/bin/ 
-d /home/jkregloh/pg_data/ -D /usr/local/pgsql/data/ -p 5452 -P 5451 -c


As you can see the data and binary files for 9.0 are in 
/home/jkregloh/pg_bin/ and /home/jkregloh/pg_data/, while the 9.3 
resides in the default location.


When running the check it reports that both clusters are compatible. 
Once the actual process starts it will work fine until it starts up 
the 9.3 to copy data over. The problem that I am having is that 
pg_upgrade is creating the 93 files under the old directory and 
not the new one. So when 9.3 goes to import it doesn't find anything.


Now, both versions can't share the same /data directory for obvious 
reasons. Is there any way to make pg_upgrade actually export the new 
9.3 files into the 9.3 directory supplied in the pg_upgrade command? I 
am also open to any other upgrade ideas.


Thanks,
Joseph


Hi Joseph,

Can you post your actual command syntax when you run the upgrade (not 
the check)?  Maybe there'll be something wrong there we can spot.

When I did it recently, I used something along the lines of:

(PG93path)/pg_upgrade -d /opt/rg/data/pgsql90 -D /opt/rg/data/pgsql93 -b 
/(path to PG90 binaries)/bin -B /(path to 93 binaries) -v -p (oldport) 
-P (newport)


Ziggy