Обсуждение: 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

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

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

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

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

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