Обсуждение: accidentally deleted data directory
See if there are processes running for user "postgres" that look
like database processes and kill them.
Then try again.
What exactly is the error message?
Yours,
Laurenz Albe
Hi
ps –edf shows no postgres processes:
-bash-4.1$ ps -edf | grep postgres
root 6412 6249 0 18:22 pts/0 00:00:00 su - postgres
postgres 6413 6412 0 18:22 pts/0 00:00:00 -bash
postgres 6465 6413 1 18:27 pts/0 00:00:00 ps -edf
postgres 6466 6413 0 18:27 pts/0 00:00:00 grep postgres
Ran the following command as postgres user
-bash-4.1$ /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data
-bash-4.1$
pgstartup.log is empty
-bash-4.1$ pwd
/var/lib/pgsql/9.2
-bash-4.1$ ls
backups data logfile pgstartup.log
-bash-4.1$ ls -al
total 20
drwx------. 4 postgres postgres 4096 Sep 23 18:21 .
drwx------. 3 postgres postgres 4096 Sep 23 18:18 ..
drwx------. 2 postgres postgres 4096 Apr 1 19:41 backups
drwx------. 15 postgres postgres 4096 Sep 23 18:28 data
-rw-------. 1 postgres postgres 409 Sep 22 11:44 logfile
-rw------- 1 postgres postgres 0 Sep 23 18:20 pgstartup.log
-bash-4.1$
Is there something else I can try?
I don’t have any data to restore. This is an installation which is only a couple of weeks old. I can uninstall and reinstall if that is easier.
Where can I find the uninstaller ?
Thanks
Thara.
If pgstartup.log is empty that points at an issue with the configuration file. From memory, when you do an initdb it recreates the configuration files in the /var/lib/pg... directory. I would look at the main control file and the pg_hba.conf and try to start the database passing it the name of the main conf file as a parameter (as well as the database directory).
Cheers,
Cliff
On Tue, Sep 24, 2013 at 10:36 AM, Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
See if there are processes running for user "postgres" that looklike database processes and kill them.Then try again.What exactly is the error message?Yours,Laurenz AlbeHips –edf shows no postgres processes:-bash-4.1$ ps -edf | grep postgresroot 6412 6249 0 18:22 pts/0 00:00:00 su - postgrespostgres 6413 6412 0 18:22 pts/0 00:00:00 -bashpostgres 6465 6413 1 18:27 pts/0 00:00:00 ps -edfpostgres 6466 6413 0 18:27 pts/0 00:00:00 grep postgresRan the following command as postgres user-bash-4.1$ /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data-bash-4.1$pgstartup.log is empty-bash-4.1$ pwd/var/lib/pgsql/9.2-bash-4.1$ lsbackups data logfile pgstartup.log-bash-4.1$ ls -altotal 20drwx------. 4 postgres postgres 4096 Sep 23 18:21 .drwx------. 3 postgres postgres 4096 Sep 23 18:18 ..drwx------. 2 postgres postgres 4096 Apr 1 19:41 backupsdrwx------. 15 postgres postgres 4096 Sep 23 18:28 data-rw-------. 1 postgres postgres 409 Sep 22 11:44 logfile-rw------- 1 postgres postgres 0 Sep 23 18:20 pgstartup.log-bash-4.1$Is there something else I can try?I don’t have any data to restore. This is an installation which is only a couple of weeks old. I can uninstall and reinstall if that is easier.Where can I find the uninstaller ?ThanksThara.
Thara Vadakkeveedu wrote: > ps –edf shows no postgres processes: > -bash-4.1$ ps -edf | grep postgres > root 6412 6249 0 18:22 pts/0 00:00:00 su - postgres > postgres 6413 6412 0 18:22 pts/0 00:00:00 -bash > postgres 6465 6413 1 18:27 pts/0 00:00:00 ps -edf > postgres 6466 6413 0 18:27 pts/0 00:00:00 grep postgres Ok. > Ran the following command as postgres user > -bash-4.1$ /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data > > pgstartup.log is empty > > -bash-4.1$ pwd > /var/lib/pgsql/9.2 > -bash-4.1$ ls > backups data logfile pgstartup.log > -bash-4.1$ ls -al > total 20 > drwx------. 4 postgres postgres 4096 Sep 23 18:21 . > drwx------. 3 postgres postgres 4096 Sep 23 18:18 .. > drwx------. 2 postgres postgres 4096 Apr 1 19:41 backups > drwx------. 15 postgres postgres 4096 Sep 23 18:28 data > -rw-------. 1 postgres postgres 409 Sep 22 11:44 logfile > -rw------- 1 postgres postgres 0 Sep 23 18:20 pgstartup.log > -bash-4.1$ > > Is there something else I can try? Yes. Look how logging is configured in /var/lib/pgsql/9.2/data/postgresql.conf (parameters log_destination, logging_collector, log_directory, log_filename). Find the log file and see what is in there. > I don’t have any data to restore. This is an installation which is only a couple of weeks old. I can > uninstall and reinstall if that is easier. I don't know if that would solve the problem. > Where can I find the uninstaller ? How did you install PostgreSQL? Yours, Laurenz Albe
Here are the settings in the postgresql.conf file:
I remember turning on logging to troubleshoot a performance issue....
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
#log_filename = 'postgresql-%a.log'
log_truncate_on_rotation=on
log_rotation_age = 1d
log_rotation_age = 1d
log_rotation_size = 0
I installed postges from an rpm file (pgdg-redhat92-9.2-7.noarch.rpm) using yum install.
Thanks,
thara.
From: Albe Laurenz <laurenz.albe@wien.gv.at>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 6:41 AM
Subject: Re: [ADMIN] accidentally deleted data directory
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 6:41 AM
Subject: Re: [ADMIN] accidentally deleted data directory
Thara Vadakkeveedu wrote:
> ps –edf shows no postgres processes:
> -bash-4.1$ ps -edf | grep postgres
> root 6412 6249 0 18:22 pts/0 00:00:00 su - postgres
> postgres 6413 6412 0 18:22 pts/0 00:00:00 -bash
> postgres 6465 6413 1 18:27 pts/0 00:00:00 ps -edf
> postgres 6466 6413 0 18:27 pts/0 00:00:00 grep postgres
Ok.
> Ran the following command as postgres user
> -bash-4.1$ /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data
>
> pgstartup.log is empty
>
> -bash-4.1$ pwd
> /var/lib/pgsql/9.2
> -bash-4.1$ ls
> backups data logfile pgstartup.log
> -bash-4.1$ ls -al
> total 20
> drwx------. 4 postgres postgres 4096 Sep 23 18:21 .
> drwx------. 3 postgres postgres 4096 Sep 23 18:18 ..
> drwx------. 2 postgres postgres 4096 Apr 1 19:41 backups
> drwx------. 15 postgres postgres 4096 Sep 23 18:28 data
> -rw-------. 1 postgres postgres 409 Sep 22 11:44 logfile
> -rw------- 1 postgres postgres 0 Sep 23 18:20 pgstartup.log
> -bash-4.1$
>
> Is there something else I can try?
Yes.
Look how logging is configured in /var/lib/pgsql/9.2/data/postgresql.conf
(parameters log_destination, logging_collector, log_directory, log_filename).
Find the log file and see what is in there.
> I don’t have any data to restore. This is an installation which is only a couple of weeks old. I can
> uninstall and reinstall if that is easier.
I don't know if that would solve the problem.
> Where can I find the uninstaller ?
How did you install PostgreSQL?
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
This is what I find in pg_log/postgresql-Mon.log file (contents relate to my attempts to start postgres yesterday)
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 2361) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6323) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6346) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
"postgresql-Mon.log" 49L, 2709C
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 2361) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6323) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6346) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
"postgresql-Mon.log" 49L, 2709C
thanks
thara.
From: Thara Vadakkeveedu <tharagv@yahoo.com>
To: Albe Laurenz <laurenz.albe@wien.gv.at>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 9:47 AM
Subject: Re: [ADMIN] accidentally deleted data directory
To: Albe Laurenz <laurenz.albe@wien.gv.at>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 9:47 AM
Subject: Re: [ADMIN] accidentally deleted data directory
Here are the settings in the postgresql.conf file:
I remember turning on logging to troubleshoot a performance issue....
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
#log_filename = 'postgresql-%a.log'
log_truncate_on_rotation=on
log_rotation_age = 1d
log_rotation_age = 1d
log_rotation_size = 0
I installed postges from an rpm file (pgdg-redhat92-9.2-7.noarch.rpm) using yum install.
Thanks,
thara.
From: Albe Laurenz <laurenz.albe@wien.gv.at>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 6:41 AM
Subject: Re: [ADMIN] accidentally deleted data directory
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 6:41 AM
Subject: Re: [ADMIN] accidentally deleted data directory
Thara Vadakkeveedu wrote:
> ps –edf shows no postgres processes:
> -bash-4.1$ ps -edf | grep postgres
> root 6412 6249 0 18:22 pts/0 00:00:00 su - postgres
> postgres 6413 6412 0 18:22 pts/0 00:00:00 -bash
> postgres 6465 6413 1 18:27 pts/0 00:00:00 ps -edf
> postgres 6466 6413 0 18:27 pts/0 00:00:00 grep postgres
Ok.
> Ran the following command as postgres user
> -bash-4.1$ /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data
>
> pgstartup.log is empty
>
> -bash-4.1$ pwd
> /var/lib/pgsql/9.2
> -bash-4.1$ ls
> backups data logfile pgstartup.log
> -bash-4.1$ ls -al
> total 20
> drwx------. 4 postgres postgres 4096 Sep 23 18:21 .
> drwx------. 3 postgres postgres 4096 Sep 23 18:18 ..
> drwx------. 2 postgres postgres 4096 Apr 1 19:41 backups
> drwx------. 15 postgres postgres 4096 Sep 23 18:28 data
> -rw-------. 1 postgres postgres 409 Sep 22 11:44 logfile
> -rw------- 1 postgres postgres 0 Sep 23 18:20 pgstartup.log
> -bash-4.1$
>
> Is there something else I can try?
Yes.
Look how logging is configured in /var/lib/pgsql/9.2/data/postgresql.conf
(parameters log_destination, logging_collector, log_directory, log_filename).
Find the log file and see what is in there.
> I don’t have any data to restore. This is an installation which is only a couple of weeks old. I can
> uninstall and reinstall if that is easier.
I don't know if that would solve the problem.
> Where can I find the uninstaller ?
How did you install PostgreSQL?
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Do I need to run pg_resetxlog to fix the corrupted pg_control file issue?
I can't find it in my 9.2 installation...
thanks
TG
From: Thara Vadakkeveedu <tharagv@yahoo.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Albe Laurenz <laurenz.albe@wien.gv.at>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 9:53 AM
Subject: Re: [ADMIN] accidentally deleted data directory
To: Thara Vadakkeveedu <tharagv@yahoo.com>; Albe Laurenz <laurenz.albe@wien.gv.at>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 9:53 AM
Subject: Re: [ADMIN] accidentally deleted data directory
This is what I find in pg_log/postgresql-Mon.log file (contents relate to my attempts to start postgres yesterday)
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 2361) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6323) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6346) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
"postgresql-Mon.log" 49L, 2709C
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 2361) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6323) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
LOG: invalid primary checkpoint record
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 6346) was terminated by signal 6: Aborted
LOG: aborting startup due to startup process failure
LOG: database system was shut down at 2013-09-22 12:20:51 EDT
LOG: invalid magic number 0000 in log file 0, segment 39, offset 0
"postgresql-Mon.log" 49L, 2709C
thanks
thara.
From: Thara Vadakkeveedu <tharagv@yahoo.com>
To: Albe Laurenz <laurenz.albe@wien.gv.at>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 9:47 AM
Subject: Re: [ADMIN] accidentally deleted data directory
To: Albe Laurenz <laurenz.albe@wien.gv.at>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 9:47 AM
Subject: Re: [ADMIN] accidentally deleted data directory
Here are the settings in the postgresql.conf file:
I remember turning on logging to troubleshoot a performance issue....
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
#log_filename = 'postgresql-%a.log'
log_truncate_on_rotation=on
log_rotation_age = 1d
log_rotation_age = 1d
log_rotation_size = 0
I installed postges from an rpm file (pgdg-redhat92-9.2-7.noarch.rpm) using yum install.
Thanks,
thara.
From: Albe Laurenz <laurenz.albe@wien.gv.at>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 6:41 AM
Subject: Re: [ADMIN] accidentally deleted data directory
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 6:41 AM
Subject: Re: [ADMIN] accidentally deleted data directory
Thara Vadakkeveedu wrote:
> ps –edf shows no postgres processes:
> -bash-4.1$ ps -edf | grep postgres
> root 6412 6249 0 18:22 pts/0 00:00:00 su - postgres
> postgres 6413 6412 0 18:22 pts/0 00:00:00 -bash
> postgres 6465 6413 1 18:27 pts/0 00:00:00 ps -edf
> postgres 6466 6413 0 18:27 pts/0 00:00:00 grep postgres
Ok.
> Ran the following command as postgres user
> -bash-4.1$ /usr/pgsql-9.2/bin/postgres -D /var/lib/pgsql/9.2/data
>
> pgstartup.log is empty
>
> -bash-4.1$ pwd
> /var/lib/pgsql/9.2
> -bash-4.1$ ls
> backups data logfile pgstartup.log
> -bash-4.1$ ls -al
> total 20
> drwx------. 4 postgres postgres 4096 Sep 23 18:21 .
> drwx------. 3 postgres postgres 4096 Sep 23 18:18 ..
> drwx------. 2 postgres postgres 4096 Apr 1 19:41 backups
> drwx------. 15 postgres postgres 4096 Sep 23 18:28 data
> -rw-------. 1 postgres postgres 409 Sep 22 11:44 logfile
> -rw------- 1 postgres postgres 0 Sep 23 18:20 pgstartup.log
> -bash-4.1$
>
> Is there something else I can try?
Yes.
Look how logging is configured in /var/lib/pgsql/9.2/data/postgresql.conf
(parameters log_destination, logging_collector, log_directory, log_filename).
Find the log file and see what is in there.
> I don’t have any data to restore. This is an installation which is only a couple of weeks old. I can
> uninstall and reinstall if that is easier.
I don't know if that would solve the problem.
> Where can I find the uninstaller ?
How did you install PostgreSQL?
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Thara Vadakkeveedu wrote: > Do I need to run pg_resetxlog to fix the corrupted pg_control file issue? No, since you don't mind starting afresh, just delete the data directory and run initdb. Yours, Laurenz Albe
Thank you!
I deleted data directory again and then ran initdb.
This time service started successfully.
TG
From: Albe Laurenz <laurenz.albe@wien.gv.at>
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 10:11 AM
Subject: Re: [ADMIN] accidentally deleted data directory
To: Thara Vadakkeveedu <tharagv@yahoo.com>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 10:11 AM
Subject: Re: [ADMIN] accidentally deleted data directory
Thara Vadakkeveedu wrote:
> Do I need to run pg_resetxlog to fix the corrupted pg_control file issue?
No, since you don't mind starting afresh,
just delete the data directory and run initdb.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Hi
I am trying to connect to the database on a linux server from pgadmin on my windows desktop.
I see "Error connecting to the server: Fatal: password authentication failed for user "postgres"
I specified my linux server name for the host
port : 5432
Maintenance DB : postgres
username:postgres
passowrd: a passwod I set (using alter user postgres password command. I changed password as user postgres)
How can I connect to the postgres database ?
thanks,
thara.
I need to create a database and a user and make this new user the owner of this new database.
Since I cannot access postgres db from pgadmin client on my desktop,
I tried to do the same from the command line on the linux db server:
su - postgres
-bash-4.1$ psql -d postgres
postgres=# create user puser password 'xxxxxx'
postgres=# create database pfdb owner puser
If I now try to list databases using \l
it lists postgres, template0 and template1 but not the new database I created...
How can I create a new database successfully ? It will be easier for me to try this from the pgadmin client.
Thanks
TG
From: Thara Vadakkeveedu <tharagv@yahoo.com>
To: Albe Laurenz <laurenz.albe@wien.gv.at>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 8:37 PM
Subject: connecting to the database from pgadmin client
To: Albe Laurenz <laurenz.albe@wien.gv.at>; "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Sent: Tuesday, September 24, 2013 8:37 PM
Subject: connecting to the database from pgadmin client
Hi
I am trying to connect to the database on a linux server from pgadmin on my windows desktop.
I see "Error connecting to the server: Fatal: password authentication failed for user "postgres"
I specified my linux server name for the host
port : 5432
Maintenance DB : postgres
username:postgres
passowrd: a passwod I set (using alter user postgres password command. I changed password as user postgres)
How can I connect to the postgres database ?
thanks,
thara.
I wanted to take a backup of my database.
pg_dump throws a server mismatch version error
# cd backups
# su - postgres
-bash-4.1$ pg_dump mydb > mydbfile
pg_dump: server version: 9.2.4; pg_dump version: 8.4.13
pg_dump: aborting because of server version mismatch
This is a fresh install of postgresql on a red hat linux server that did not have any previous database installed on it . I installed postgresql 9.2 from an rpm (pgdg-redhat92-9.2-7.noarch.rpm ) using yum install.
Why am I seeing this error?
How can I take a backup of my database? There are two tables that have 20,000+ rows in it, that I want to backup and restore when needed.
thanks
thara.
Hello, first excuse my English
U cant do a backup whit a Client minor version than the server
U must have same version to do the backup, so U need upgrade your client (pg_dump 8.4.13) to at least 9.2.4
See U
U cant do a backup whit a Client minor version than the server
U must have same version to do the backup, so U need upgrade your client (pg_dump 8.4.13) to at least 9.2.4
See U
El 25/09/2013 15:55, Thara Vadakkeveedu escribió:
Hi
I wanted to take a backup of my database.
pg_dump throws a server mismatch version error
# cd backups
# su - postgres
-bash-4.1$ pg_dump mydb > mydbfile
pg_dump: server version: 9.2.4; pg_dump version: 8.4.13
pg_dump: aborting because of server version mismatch
This is a fresh install of postgresql on a red hat linux server that did not have any previous database installed on it . I installed postgresql 9.2 from an rpm (pgdg-redhat92-9.2-7.noarch.rpm ) using yum install.
Why am I seeing this error?
How can I take a backup of my database? There are two tables that have 20,000+ rows in it, that I want to backup and restore when needed.
thanks
thara.
Looks like the real problem here is that you got a version of postgresql installed with the OS, and then you also installed the 9.2 RPMs. Uninstall the postgres that came with the OS and/or specify the full path to the 9.2 pg_dump. On Wed, Sep 25, 2013 at 12:47 PM, Alejandro Brust <alejandrob@pasteleros.org.ar> wrote: > Hello, first excuse my English > U cant do a backup whit a Client minor version than the server > U must have same version to do the backup, so U need upgrade your client > (pg_dump 8.4.13) to at least 9.2.4 > > > See U > > > El 25/09/2013 15:55, Thara Vadakkeveedu escribió: > > > Hi > I wanted to take a backup of my database. > > pg_dump throws a server mismatch version error > > # cd backups > # su - postgres > -bash-4.1$ pg_dump mydb > mydbfile > pg_dump: server version: 9.2.4; pg_dump version: 8.4.13 > pg_dump: aborting because of server version mismatch > > This is a fresh install of postgresql on a red hat linux server that did not > have any previous database installed on it . I installed postgresql 9.2 from > an rpm (pgdg-redhat92-9.2-7.noarch.rpm ) using yum install. > > Why am I seeing this error? > How can I take a backup of my database? There are two tables that have > 20,000+ rows in it, that I want to backup and restore when needed. > > thanks > thara. > > -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ L. Friedman netllama@gmail.com LlamaLand https://netllama.linux-sxs.org
Hi
I did not install the client separately. I assumed i would get the right client as part of the 9.2 Yum install...
I see psql is an older version too.
# su - postgres
-bash-4.1$ psql -d postgres
psql (8.4.13, server 9.2.4)
WARNING: psql version 8.4, server version 9.2.
Some psql features might not work.
When I installed postgresql-9.2 using Yum (yum install postgresql92 postgresql92-server)
The following 3 packages were installed:
Is there a separate package I should have installed for the 9.2 client ?
Thanks,
TG
From: Alejandro Brust <alejandrob@pasteleros.org.ar>
To: pgsql-admin@postgresql.org
Sent: Wednesday, September 25, 2013 3:47 PM
Subject: Re: [ADMIN] pd_dump server mismatch error
I did not install the client separately. I assumed i would get the right client as part of the 9.2 Yum install...
I see psql is an older version too.
# su - postgres
-bash-4.1$ psql -d postgres
psql (8.4.13, server 9.2.4)
WARNING: psql version 8.4, server version 9.2.
Some psql features might not work.
When I installed postgresql-9.2 using Yum (yum install postgresql92 postgresql92-server)
The following 3 packages were installed:
postgresql92 x86_64 9.2.4-1PGDG.rhel6 pgdg92 970 k
postgresql92-server x86_64 9.2.4-1PGDG.rhel6 pgdg92 3.8 M
postgresql92-libs x86_64 9.2.4-1PGDG.rhel6 pgdg92 185 k
I assumed this took care of installing the right client. I run the client from the same machine where the server is installed. Is there a separate package I should have installed for the 9.2 client ?
Thanks,
TG
From: Alejandro Brust <alejandrob@pasteleros.org.ar>
To: pgsql-admin@postgresql.org
Sent: Wednesday, September 25, 2013 3:47 PM
Subject: Re: [ADMIN] pd_dump server mismatch error
Hello, first excuse my English
U cant do a backup whit a Client minor version than the server
U must have same version to do the backup, so U need upgrade your client (pg_dump 8.4.13) to at least 9.2.4
See U
U cant do a backup whit a Client minor version than the server
U must have same version to do the backup, so U need upgrade your client (pg_dump 8.4.13) to at least 9.2.4
See U
El 25/09/2013 15:55, Thara Vadakkeveedu escribió:
Hi
I wanted to take a backup of my database.
pg_dump throws a server mismatch version error
# cd backups
# su - postgres
-bash-4.1$ pg_dump mydb > mydbfile
pg_dump: server version: 9.2.4; pg_dump version: 8.4.13
pg_dump: aborting because of server version mismatch
This is a fresh install of postgresql on a red hat linux server that did not have any previous database installed on it . I installed postgresql 9.2 from an rpm (pgdg-redhat92-9.2-7.noarch.rpm ) using yum install.
Why am I seeing this error?
How can I take a backup of my database? There are two tables that have 20,000+ rows in it, that I want to backup and restore when needed.
thanks
thara.
Thara Vadakkeveedu wrote: > I need to create a database and a user and make this new user the owner of this new database. > > Since I cannot access postgres db from pgadmin client on my desktop, > > > I tried to do the same from the command line on the linux db server: > > su - postgres > -bash-4.1$ psql -d postgres > > postgres=# create user puser password 'xxxxxx' > > postgres=# create database pfdb owner puser > > If I now try to list databases using \l > it lists postgres, template0 and template1 but not the new database I created... > > How can I create a new database successfully ? It will be easier for me to try this from the pgadmin client. ... but it is better with psql first because that is simpler. I see that there are no semicolons at the end of your statements. That would mean that the statements don't get executed at all. Is this just a copy/paste error? Could you copy the complete session (what exactly you typed and what response you got)? Yours, Laurenz Albe
Hi,
I found pg_dump under /usr/pgsql-9.2/bin/ and /usr/bin, but both of them show version as 8.4.13.
# su - postgres
-bash-4.1$ cd /usr/pgsql-9.2/bin
-bash-4.1$ pg_dump --version
pg_dump (PostgreSQL) 8.4.13
-bash-4.1$ cd /usr/bin
-bash-4.1$ pg_dump --version
pg_dump (PostgreSQL) 8.4.13
How can I upgrade pg_dump to 9.2 ?
Thanks,
TG
# su - postgres
-bash-4.1$ cd /usr/pgsql-9.2/bin
-bash-4.1$ pg_dump --version
pg_dump (PostgreSQL) 8.4.13
-bash-4.1$ cd /usr/bin
-bash-4.1$ pg_dump --version
pg_dump (PostgreSQL) 8.4.13
How can I upgrade pg_dump to 9.2 ?
Thanks,
TG
Hi,
When you say preinstalled with the system, you mean preinstalled with RedHat Linux?
I seem to have the right version ... I had to use the full path to identify the version.
-bash-4.1$ /usr/pgsql-9.2/bin/pg_dump --version
pg_dump (PostgreSQL) 9.2.4
From: Craig James <cjames@emolecules.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>
Sent: Wednesday, September 25, 2013 5:32 PM
Subject: Re: [ADMIN] pd_dump server mismatch error
When you say preinstalled with the system, you mean preinstalled with RedHat Linux?
I seem to have the right version ... I had to use the full path to identify the version.
-bash-4.1$ /usr/pgsql-9.2/bin/pg_dump --version
pg_dump (PostgreSQL) 9.2.4
How can I find the package name for the older version?
Thanks!
Thara.
From: Craig James <cjames@emolecules.com>
To: Thara Vadakkeveedu <tharagv@yahoo.com>
Sent: Wednesday, September 25, 2013 5:32 PM
Subject: Re: [ADMIN] pd_dump server mismatch error
On Wed, Sep 25, 2013 at 2:15 PM, Thara Vadakkeveedu <tharagv@yahoo.com> wrote:
Hi
I did not install the client separately. I assumed i would get the right client as part of the 9.2 Yum install...
You did get the right client, but that's not what you are running. You are running the preinstalled Postgres which came pre-installed on your system. Type "which pg_dump" to see where it is located. Your best bet is to use your package manager to remove the preinstalled version, as otherwise it's going to keep giving you trouble.
Craig
Craig
I see psql is an older version too.
# su - postgres
-bash-4.1$ psql -d postgres
psql (8.4.13, server 9.2.4)
WARNING: psql version 8.4, server version 9.2.
Some psql features might not work.
When I installed postgresql-9.2 using Yum (yum install postgresql92 postgresql92-server)
The following 3 packages were installed:postgresql92 x86_64 9.2.4-1PGDG.rhel6 pgdg92 970 kpostgresql92-server x86_64 9.2.4-1PGDG.rhel6 pgdg92 3.8 Mpostgresql92-libs x86_64 9.2.4-1PGDG.rhel6 pgdg92 185 kI assumed this took care of installing the right client. I run the client from the same machine where the server is installed.
Is there a separate package I should have installed for the 9.2 client ?
Thanks,
TG
From: Alejandro Brust <alejandrob@pasteleros.org.ar>
To: pgsql-admin@postgresql.org
Sent: Wednesday, September 25, 2013 3:47 PM
Subject: Re: [ADMIN] pd_dump server mismatch errorHello, first excuse my English
U cant do a backup whit a Client minor version than the server
U must have same version to do the backup, so U need upgrade your client (pg_dump 8.4.13) to at least 9.2.4
See UEl 25/09/2013 15:55, Thara Vadakkeveedu escribió:Hi
I wanted to take a backup of my database.
pg_dump throws a server mismatch version error
# cd backups
# su - postgres
-bash-4.1$ pg_dump mydb > mydbfile
pg_dump: server version: 9.2.4; pg_dump version: 8.4.13
pg_dump: aborting because of server version mismatch
This is a fresh install of postgresql on a red hat linux server that did not have any previous database installed on it . I installed postgresql 9.2 from an rpm (pgdg-redhat92-9.2-7.noarch.rpm ) using yum install.
Why am I seeing this error?
How can I take a backup of my database? There are two tables that have 20,000+ rows in it, that I want to backup and restore when needed.
thanks
thara.
2013/9/26 Thara Vadakkeveedu <tharagv@yahoo.com>: > Hi, > When you say preinstalled with the system, you mean preinstalled with RedHat > Linux? > > I seem to have the right version ... I had to use the full path to identify > the version. > > -bash-4.1$ /usr/pgsql-9.2/bin/pg_dump --version > pg_dump (PostgreSQL) 9.2.4 > > How can I find the package name for the older version? This: rpm -qf $(which pg_dump) should show the package which the pg_dump binary in your default path belongs to. Regards Ian Barwick
Hi
Is there a way to get cpu utilization of various sql statements during a specific time period on postgresql ?
Say I start a performance test, I would like to collect a top sql report at the end of the test (like in sql server) that gives details of the top cpu consuming sqls..
Also wondering if there is a sql profiler on postgressql that indicates the number of rows read and written to by sql statements, cpu cost, elapsed time etc during a profiled session.
thanks,
thara.
On Mon, Oct 21, 2013 at 10:18 AM, Thara Vadakkeveedu <tharagv@yahoo.com> wrote: > Hi > Is there a way to get cpu utilization of various sql statements during a > specific time period on postgresql ? > Say I start a performance test, I would like to collect a top sql report at > the end of the test (like in sql server) that gives details of the top cpu > consuming sqls.. > Also wondering if there is a sql profiler on postgressql that indicates the > number of rows read and written to by sql statements, cpu cost, elapsed time > etc during a profiled session. Maybe not the exact CPU usage but you can get a nice report on the most expensive queries (either individually expensive or as a group etc) from pgbadger. http://dalibo.github.io/pgbadger/
On Mon, Oct 21, 2013 at 09:18:18AM -0700, Thara Vadakkeveedu wrote: > Hi > Is there a way to get cpu utilization of various sql statements during a specific time period on postgresql ? > Say I start a performance test, I would like to collect a top sql report at the end of the test (like in sql server) thatgives details of the top cpu consuming sqls.. > Also wondering if there is a sql profiler on postgressql that indicates the number of rows read and written to by sql statements,cpu cost, elapsed time etc during a profiled session. > thanks, > thara. Hi Thara, Have you looked at pgbadger? Cheers, Ken
Subject: [ADMIN] CPU usage by sqls Hi Is there a way to get cpu utilization of various sql statements during a specific time period on postgresql ? Say I start a performance test, I would like to collect a top sql report at the end of the test (like in sql server) thatgives details of the top cpu consuming sqls.. Also wondering if there is a sql profiler on postgressql that indicates the number of rows read and written to by sql statements,cpu cost, elapsed time etc during a profiled session. thanks, thara. Take a look at pg_stat_statements extension. Regards, Igor Neyman