pg_sample

On Wed, Oct 19, 2016 at 9:24 AM, Patrick B <patrickbakerbr@gmail.com> wrote:

However, this new database test server doesn't need to have all the data. I
would like to have only the first 100 rows(example) of each table in my
database.

I'm using pg_sample to do that, but unfortunately it doesn't work well.
It doesn't get the first 100 rows. It gets random 100 rows.

Why aren't 100 random rows enough to fulfill what you are looking for?
What you are trying here is to test the server with some sample data,
no? In this case, having the first 100 rows, or a set of random ones
should not matter much (never tried pg_sample to be honest).
--
Michael

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

2016-10-19 13:39 GMT+13:00 Michael Paquier <michael.paquier@gmail.com>:

On Wed, Oct 19, 2016 at 9:24 AM, Patrick B <patrickbakerbr@gmail.com>
wrote:

However, this new database test server doesn't need to have all the

data. I

would like to have only the first 100 rows(example) of each table in my
database.

I'm using pg_sample to do that, but unfortunately it doesn't work well.
It doesn't get the first 100 rows. It gets random 100 rows.

Why aren't 100 random rows enough to fulfill what you are looking for?
What you are trying here is to test the server with some sample data,
no? In this case, having the first 100 rows, or a set of random ones
should not matter much (never tried pg_sample to be honest).
--
Michael

Actually it does matter because there is some essential data that has to be
in there so the code can work.

On 10/18/2016 06:30 PM, Patrick B wrote:

2016-10-19 13:39 GMT+13:00 Michael Paquier <michael.paquier@gmail.com
<mailto:michael.paquier@gmail.com>>:

On Wed, Oct 19, 2016 at 9:24 AM, Patrick B <patrickbakerbr@gmail.com
<mailto:patrickbakerbr@gmail.com>> wrote:

However, this new database test server doesn't need to have all the data. I
would like to have only the first 100 rows(example) of each table in my
database.

I'm using pg_sample to do that, but unfortunately it doesn't work well.
It doesn't get the first 100 rows. It gets random 100 rows.

Why aren't 100 random rows enough to fulfill what you are looking for?
What you are trying here is to test the server with some sample data,
no? In this case, having the first 100 rows, or a set of random ones
should not matter much (never tried pg_sample to be honest).
--
Michael

Actually it does matter because there is some essential data that has to
be in there so the code can work.

Well random does not know essential, it is after all random. If you want
to test specific cases then you will need to build appropriate data sets.

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Tue, Oct 18, 2016 at 10:21 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 10/18/2016 06:30 PM, Patrick B wrote:

2016-10-19 13:39 GMT+13:00 Michael Paquier <michael.paquier@gmail.com
<mailto:michael.paquier@gmail.com>>:

On Wed, Oct 19, 2016 at 9:24 AM, Patrick B <patrickbakerbr@gmail.com
<mailto:patrickbakerbr@gmail.com>> wrote:

However, this new database test server doesn't need to have all the

data. I

would like to have only the first 100 rows(example) of each table

in my

database.

I'm using pg_sample to do that, but unfortunately it doesn't work

well.

It doesn't get the first 100 rows. It gets random 100 rows.

Why aren't 100 random rows enough to fulfill what you are looking for?
What you are trying here is to test the server with some sample data,
no? In this case, having the first 100 rows, or a set of random ones
should not matter much (never tried pg_sample to be honest).
--
Michael

Actually it does matter because there is some essential data that has to
be in there so the code can work.

Well random does not know essential, it is after all random. If you want
to test specific cases then you will need to build appropriate data sets.

--
Adrian Klaver
adrian.klaver@aklaver.com

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

The following query should generate statements you can use to get the first
100 rows of every table.
You may need to tweak a bit as order is not guaranteed.

SELECT 'COPY ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname)
|| ' TO '''
-- || 'C:\temp\'
|| '/tmp/'
|| quote_ident(n.nspname) || '_' || quote_ident(c.relname) || '.csv' ||
''''
|| ' WITH CSV HEADER FORCE_QUOTE *;'
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'sql_%'
LIMIT 100;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Patrick B <patrickbakerbr@gmail.com> writes:
...

However, this new database test server doesn't need to have all the data. I
would like to have only the first 100 rows(example) of each table in my
database.

...

This should do what you ask.

If the order does not matter, leave out the ORDER BY.

This assumes everything of interest is in the public schema.

$ createdb testdb
$ pg_dump realdb --schema-only | psql -q testdb
$ psql realdb

psql> \o dump.some.rows.sh
psql> select format($$psql realdb -c 'COPY (select * from %I order by 1 limit %s) TO STDOUT' | psql testdb -c 'COPY %I FROM STDIN' $$, table_name, 100, table_name)
from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE';
psql> \q

$ sh dump.some.rows.sh

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201610182256
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlgG4NkACgkQvJuQZxSWSsge4ACePhBOBtBFnGNxXt5qpY7X+w3o
d04AoKTzAgxcaqy8qfIE0LPuzG9x0KIU
=sS+m
-----END PGP SIGNATURE-----

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

Hello

On 10/19/2016 04:58 AM, Greg Sabino Mullane wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Patrick B <patrickbakerbr@gmail.com> writes:
...

However, this new database test server doesn't need to have all the data. I
would like to have only the first 100 rows(example) of each table in my
database.

...

This should do what you ask.

If the order does not matter, leave out the ORDER BY.

This assumes everything of interest is in the public schema.

$ createdb testdb
$ pg_dump realdb --schema-only | psql -q testdb
$ psql realdb

psql> \o dump.some.rows.sh
psql> select format($$psql realdb -c 'COPY (select * from %I order by 1 limit %s) TO STDOUT' | psql testdb -c 'COPY %I FROM STDIN' $$, table_name, 100, table_name)
from information_schema.tables where table_schema = 'public' and table_type = 'BASE TABLE';
psql> \q

$ sh dump.some.rows.sh

I may be overseeing something, but what about dependencies between
tables, sequencies, indexes, etc.? I guess that if one takes the first
100 rows of a table referenced by another table, there is no guarantee
that in the first 100 rows of the referencing table there will not be
some foreign key that does not exist.

Regards
Charles

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201610182256
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlgG4NkACgkQvJuQZxSWSsge4ACePhBOBtBFnGNxXt5qpY7X+w3o
d04AoKTzAgxcaqy8qfIE0LPuzG9x0KIU
=sS+m
-----END PGP SIGNATURE-----

--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+-----------------------+
| ____ ______ ___ |
| / )/ \/ \ |
| ( / __ _\ ) |
| \ (/ o) ( o) ) |
| \_ (_ ) \ ) _/ |
| \ /\_/ \)/ |
| \/ <//| |\\> |
| _| | |
| \|_/ |
| |
| PostgreSQL 1996-2016 |
| 20 Years of Success |
| |
+-----------------------+

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

On 10/18/2016 08:15 PM, Charles Clavadetscher wrote:

Hello

On 10/19/2016 04:58 AM, Greg Sabino Mullane wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Patrick B <patrickbakerbr@gmail.com> writes:
...

However, this new database test server doesn't need to have all the
data. I
would like to have only the first 100 rows(example) of each table in my
database.

...

This should do what you ask.

If the order does not matter, leave out the ORDER BY.

This assumes everything of interest is in the public schema.

$ createdb testdb
$ pg_dump realdb --schema-only | psql -q testdb
$ psql realdb

psql> \o dump.some.rows.sh
psql> select format($$psql realdb -c 'COPY (select * from %I order by
1 limit %s) TO STDOUT' | psql testdb -c 'COPY %I FROM STDIN' $$,
table_name, 100, table_name)
from information_schema.tables where table_schema = 'public' and
table_type = 'BASE TABLE';
psql> \q

$ sh dump.some.rows.sh

I may be overseeing something, but what about dependencies between
tables, sequencies, indexes, etc.? I guess that if one takes the first
100 rows of a table referenced by another table, there is no guarantee
that in the first 100 rows of the referencing table there will not be
some foreign key that does not exist.

Well there is:

https://github.com/18F/rdbms-subsetter

That still does not guarantee that the rows selected cover your test
cases though.

Regards
Charles

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201610182256
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlgG4NkACgkQvJuQZxSWSsge4ACePhBOBtBFnGNxXt5qpY7X+w3o
d04AoKTzAgxcaqy8qfIE0LPuzG9x0KIU
=sS+m
-----END PGP SIGNATURE-----

--
Adrian Klaver
adrian.klaver@aklaver.com

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

On Wed, Oct 19, 2016 at 01:24:10PM +1300, Patrick B wrote:

I'm using pg_sample to do that, but unfortunately it doesn't work well.
It doesn't get the first 100 rows. It gets random 100 rows.

Do you guys have any idea how could I do this?

For any relevant answer to this question you'll have to
define what "first" means in the context of "first 100 rows".

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

I may be overseeing something, but what about dependencies between
tables, sequencies, indexes, etc.? I guess that if one takes the first
100 rows of a table referenced by another table, there is no guarantee
that in the first 100 rows of the referencing table there will not be
some foreign key that does not exist.

The only dependency that should matter is foreign keys, and yeah, if you
have those, all bets are off; one would need to write something very custom
indeed to slurp out the data. I could envision some workarounds, but it
really depends on exactly what the OP is trying to achieve.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201610191401
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlgHtV8ACgkQvJuQZxSWSsgrzQCglNFhkdnfg4ECC1l3l0F/Uqt0
ID4AnjGHOTR5Tsfn8MwmyBItTrOg1w7Y
=6qOO
-----END PGP SIGNATURE-----

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

i have installed postgresql on centos.
i want to use pg_sample.

Do i need to install that separately of it's included in the postgresql

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

On 08/23/2018 12:10 AM, naveen12 wrote:

i have installed postgresql on centos.
i want to use pg_sample.

Do i need to install that separately of it's included in the postgresql

It is not included in the Postgres core or contrib packages. You will
need to install it separately.

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

--
Adrian Klaver
adrian.klaver@aklaver.com

sir from where should i install it.
I tried but i didn't found separate link for pg_sample
can you help me in this

thanks

On Thu, Aug 23, 2018 at 6:34 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 08/23/2018 12:10 AM, naveen12 wrote:

i have installed postgresql on centos.
i want to use pg_sample.

Do i need to install that separately of it's included in the postgresql

It is not included in the Postgres core or contrib packages. You will need
to install it separately.

--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f184378
0.html

--
Adrian Klaver
adrian.klaver@aklaver.com

--
--
With Regards
Naveen Dabas
Ph. 9017298370

--
*Important Disclaimer:* Information contained in this email is for the
recipient primarily addressed to. If you are not the primary recipient or
are not supposed to receive this email, you are advised to kindly delete
the email or the thread and notify of the error. The logo is a registered
and copyrighted property of *ACTAS TECHNOLOGIES PRIVATE LIMITED*. Do not
use it without authorization.

On 08/23/2018 07:39 AM, Naveen Dabas wrote:

sir from where should i install it.
I tried but  i didn't found separate  link for pg_sample
can you help me in this

I am guessing it is this:

https://github.com/mla/pg_sample

--
Adrian Klaver
adrian.klaver@aklaver.com

On 08/23/2018 07:39 AM, Naveen Dabas wrote:

sir from where should i install it.
I tried but  i didn't found separate  link for pg_sample
can you help me in this

Something similar:

https://github.com/18F/rdbms-subsetter

It is Python based and can be pip installed.

thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

sir have taken pg_sample
Now i want to run pg_sample with credential but i'm getting this error

Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5
/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
/usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at
./pg_sample line 192.
BEGIN failed--compilation aborted at ./pg_sample line 192.

can you help me in this

thanks

On Thu, Aug 23, 2018 at 8:22 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 08/23/2018 07:39 AM, Naveen Dabas wrote:

sir from where should i install it.
I tried but i didn't found separate link for pg_sample
can you help me in this

Something similar:

https://github.com/18F/rdbms-subsetter

It is Python based and can be pip installed.

thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

--
--
With Regards
Naveen Dabas
Ph. 9017298370

--
*Important Disclaimer:* Information contained in this email is for the
recipient primarily addressed to. If you are not the primary recipient or
are not supposed to receive this email, you are advised to kindly delete
the email or the thread and notify of the error. The logo is a registered
and copyrighted property of *ACTAS TECHNOLOGIES PRIVATE LIMITED*. Do not
use it without authorization.

sir have taken pg_sample
Now i want to run pg_sample with credential but i'm getting this error

Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./pg_sample line 192.
BEGIN failed--compilation aborted at ./pg_sample line 192.

As is clear from the message, you need to install Perl DBI/DBD first.

Solution, execute this on your linux terminal -

$ perl -MCPAN -e 'install Bundle::DBI'
$ perl -MCPAN -e 'install DBD::Pg'

Sir i'm getting error in both commands

[root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install Bundle::DBI'
Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5
/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
/usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
BEGIN failed--compilation aborted.
[root@ip-88-8-8-17 ~]# perl -MCPAN -e 'install DBD::Pg'
Can't locate CPAN.pm in @INC (@INC contains: /usr/local/lib64/perl5
/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
/usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .).
BEGIN failed--compilation aborted.

operating system =CentOS Linux release 7.5.1804 (Core)

thanks

On Fri, Aug 24, 2018 at 6:16 PM, Abhinav Mehta <abhinav@metarain.com> wrote:

Solution, execute this on your linux terminal -

$ perl -MCPAN -e 'install Bundle::DBI'
$ perl -MCPAN -e 'install DBD::Pg'

On 24-Aug-2018, at 6:13 PM, Ravi Krishna <sravikrishna@aol.com> wrote:

sir have taken pg_sample
Now i want to run pg_sample with credential but i'm getting this error

Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5

/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
/usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at
./pg_sample line 192.

BEGIN failed--compilation aborted at ./pg_sample line 192.

As is clear from the message, you need to install Perl DBI/DBD first.

--
--
With Regards
Naveen Dabas
Ph. 9017298370

--
*Important Disclaimer:* Information contained in this email is for the
recipient primarily addressed to. If you are not the primary recipient or
are not supposed to receive this email, you are advised to kindly delete
the email or the thread and notify of the error. The logo is a registered
and copyrighted property of *ACTAS TECHNOLOGIES PRIVATE LIMITED*. Do not
use it without authorization.

sudo yum install perl-CPAN

Also do a "yum search perl-" and you should find most, if not all what you
need natively packaged for your particular Linux distro. You're better off
just sticking with the pre-packaged perl modules unless you specifically
need something special.

You'll find more modules if you also enable the EPEL yum repo by setting
enabled=1 in the first section of /etc/yum.repos.d/epel.conf and rerunning
that yum search command.

On Sun, Aug 26, 2018, 2:20 PM Naveen Dabas <naveen@paymonk.com> wrote: